<commit_before>#+TITLE: SQLAlachemy

* Table of Contents :TOC_2_gh:
 - [[#references][References]]
   - [[#column][Column]]
   - [[#declarative-api][Declarative API]]
 - [[#tutorials][Tutorials]]
   - [[#introduction-to-sqlalchemy][Introduction to SQLAlchemy]]

* References
** Column
- http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Column
- http://docs.sqlalchemy.org/en/latest/core/type_basics.html

#+BEGIN_SRC python
  # use a type with arguments
  Column('data', String(50))
  # use no arguments
  Column('level', Integer)

  # turn on autoincrement for this column despite
  # the ForeignKey()
  Column('id', ForeignKey('other.id'),
              primary_key=True, autoincrement='ignore_fk')
#+END_SRC

- [[http://docs.sqlalchemy.org/en/latest/orm/mapping_columns.html#sqlalchemy.orm.column_property][column_property]] can be used for column level options:
#+BEGIN_SRC python
  class User(Base):
      __tablename__ = 'user'
      id = Column(Integer, primary_key=True)
      firstname = Column(String(50))
      lastname = Column(String(50))
      fullname = column_property(firstname + " " + lastname)
#+END_SRC

- ~Column~ can have positional arugments of [[http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.SchemaItem][SchemaItem]], such as ~Constraint~, ~ForeignKey~, ~Sequence~, etc.

*** Float
- http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.Float
- https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html
- For mysql, set ~precision~ to ~53~ cause to make the column as double

#+BEGIN_SRC python
  # Use float with double precision
  Column(Float(precision=53))
#+END_SRC

** Declarative API
- http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/index.html
- http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/api.html
- http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/basic_use.html

#+BEGIN_SRC python
  from sqlalchemy import Column, Integer, String
  from sqlalchemy.ext.declarative import declarative_base

  Base = declarative_base()

  class SomeClass(Base):
      __tablename__ = 'some_table'
      id = Column(Integer, primary_key=True)
      name =  Column(String(50))
#+END_SRC

* Tutorials
** Introduction to SQLAlchemy<commit_msg>Write about sqlalchemy session, commit, flush<commit_after>#+TITLE: SQLAlachemy

* Table of Contents :TOC_2_gh:
 - [[#references][References]]
   - [[#column][Column]]
   - [[#declarative-api][Declarative API]]
   - [[#session][Session]]
 - [[#tutorials][Tutorials]]
   - [[#introduction-to-sqlalchemy][Introduction to SQLAlchemy]]

* References
** Column
- http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Column
- http://docs.sqlalchemy.org/en/latest/core/type_basics.html

#+BEGIN_SRC python
  # use a type with arguments
  Column('data', String(50))
  # use no arguments
  Column('level', Integer)

  # turn on autoincrement for this column despite
  # the ForeignKey()
  Column('id', ForeignKey('other.id'),
              primary_key=True, autoincrement='ignore_fk')
#+END_SRC

- [[http://docs.sqlalchemy.org/en/latest/orm/mapping_columns.html#sqlalchemy.orm.column_property][column_property]] can be used for column level options:
#+BEGIN_SRC python
  class User(Base):
      __tablename__ = 'user'
      id = Column(Integer, primary_key=True)
      firstname = Column(String(50))
      lastname = Column(String(50))
      fullname = column_property(firstname + " " + lastname)
#+END_SRC

- ~Column~ can have positional arugments of [[http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.SchemaItem][SchemaItem]], such as ~Constraint~, ~ForeignKey~, ~Sequence~, etc.

*** Float
- http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.Float
- https://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html
- For mysql, set ~precision~ to ~53~ cause to make the column as double

#+BEGIN_SRC python
  # Use float with double precision
  Column(Float(precision=53))
#+END_SRC

** Declarative API
- http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/index.html
- http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/api.html
- http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/basic_use.html

#+BEGIN_SRC python
  from sqlalchemy import Column, Integer, String
  from sqlalchemy.ext.declarative import declarative_base

  Base = declarative_base()

  class SomeClass(Base):
      __tablename__ = 'some_table'
      id = Column(Integer, primary_key=True)
      name =  Column(String(50))
#+END_SRC

** Session
- http://docs.sqlalchemy.org/en/latest/orm/session_basics.html
- http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session

~Session.begin()~ returns a ~SessionTransaction~ object:
#+BEGIN_SRC python
  class SessionTransaction(object):
      # ...
      def __enter__(self):
          return self

      def __exit__(self, type, value, traceback):
          self._assert_active(deactive_ok=True, prepared_ok=True)
          if self.session.transaction is None:
              return
          if type is None:
              try:
                  self.commit()
              except:
                  with util.safe_reraise():
                      self.rollback()
          else:
              self.rollback()
#+END_SRC

So, we can use like this (code from [[http://docs.sqlalchemy.org/en/rel_0_9/orm/session_transaction.html#autocommit-mode][here]]):
#+BEGIN_SRC python
  Session = sessionmaker(bind=engine, autocommit=True)
  session = Session()
  with session.begin():
      item1 = session.query(Item).get(1)
      item2 = session.query(Item).get(2)
      item1.foo = 'bar'
      item2.bar = 'foo'
#+END_SRC

*** Session.commit() vs Session.flush()
- http://stackoverflow.com/questions/4201455/sqlalchemy-whats-the-difference-between-flush-and-commit
- A Session object is basically an ongoing transaction of changes to a database
- ~session.commit()~ commits (persists) those changes to the database.
- ~flush()~ is always called as part of a call to commit()

#+BEGIN_SRC python
  s = Session()

  # The Foo('A') object has been added to the session.
  # It has not been committed to the database yet,
  # but is returned as part of a query.
  s.add(Foo('A'))
  print s.query(Foo).all()  # Output: [<Foo('A')>]
  s.commit()


  s2 = Session()
  s2.autoflush = False

  # The Foo('B') object is *not* returned as part of this query
  # because it hasn't been flushed yet.
  s2.add(Foo('B'))
  print s2.query(Foo).all()  # Output: [<Foo('A')>]


  # Now, Foo('B') is in the same state as Foo('A') was above.
  s2.flush()
  print s2.query(Foo).all()  # Output: [<Foo('A')>, <Foo('B')>]


  # Foo('B') has not been committed, and rolling
  # back the session's transaction removes it from the session.
  s2.rollback()
  print s2.query(Foo).all()  # Output: [<Foo('A')>]
#+END_SRC

* Tutorials
** Introduction to SQLAlchemy
{'fraction_non_alphanumeric': 0.1415446454206144, 'fraction_numerical': 0.007608383577375826, 'mean_word_length': 3.8214532871972318, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 8, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2933139', 'n_tokens_mistral': 2431, 'n_tokens_neox': 2285, 'n_words': 494}