SQLalchemy

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html

1. To connect:

>>> from sqlalchemy import create_engine >>> engine = create_engine('sqlite:///:memory:', echo=True)

1. Create declarative base class:

>>> from sqlalchemy.ext.declarative import declarative_base >>> Base = declarative_base()

1. Define details of the Table which we will be mapping:

>>> from sqlalchemy import Column, Integer, String >>> class User(Base): ... _tablename_ = 'users' # this is required at minimum! ... ... id = Column(Integer, primary_key=True) ... name = Column(String) ... fullname = Column(String) ... password = Column(String) ... ... def _repr_(self): ... return "<User(name='%s', fullname='%s', password='%s')>" % ( ... self.name, self.fullname, self.password)

1. Uses the MetaData to actually CREATE TABLEs:

>>> Base.metadata.create_all(engine)

1. Now mappings are complete.
2. Here we can create and inspect a User object:

>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword') >>> ed_user.name 'ed' >>> ed_user.password 'edspassword' >>> str(ed_user.id) # This won't have an id yet. 'None'

1. SESSIONS:
2. Now can create a session, the ORM's "handle" to the database.

>>> from sqlalchemy.orm import sessionmaker >>> Session = sessionmaker(bind=engine)

• nb: if the app doesn't have an Engine yet use:
  • >>> Session = sessionmaker()
• later: can connect to the engine with:
  • >>> Session.configure(bind=engine) # once engine is available

1. When you need to have a conversation with the database
2. you instantiate a Session, like:

>>> session = Session() # Things will not happen until commit or query.

1. For instance: To persist our User object, we 'add()' it to our Session

>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword') >>> session.add(ed_user) # Now the instance is pending, until a flush.

1. If we do a query the objects will get flushed into the database:

>>> our_user = session.query(User).filter_by(name='ed').first() >>> our_user <User(name='ed', fullname='Ed Jones', password='edspassword')>

1. To add a bunch of rows:

>>> session.add_all([ ... User(name='wendy', fullname='Wendy Williams', password='foobar'), ... User(name='mary', fullname='Mary Contrary', password='xxg527'), ... User(name='fred', fullname='Fred Flinstone', password='blah')])

1. Change Ed's password:

>>> ed_user.password = 'f8s7ccs'

1. Has Ed Jones been modified?

>>> session.dirty IdentitySet([<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>])

1. Here is the new things that will be commited to the db at some time:

>>> session.new # doctest: +SKIP IdentitySet([<User(name='wendy', fullname='Wendy Williams', password='foobar')>, <User(name='mary', fullname='Mary Contrary', password='xxg527')>, <User(name='fred', fullname='Fred Flinstone', password='blah')>])

1. Finally do the commit:

>>> session.commit()

1. Looking at Ed's id, earilier was 'None' now is a '1':

>>> ed_user.id # doctest: +NORMALIZE_WHITESPACE 1

URL: http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#module-sqlalchemy.dialects.postgresql.psycopg2 Connect String:

postgresql+psycopg2://user:password@host:port/dbname[?key=val&key=val...]

Connection Thru Unix Domain:

create_engine("postgresql+psycopg2://user:pass@/dbname")

• the hostname is omitted.
• default: connect to Unix-domain socket in /tmp
• override the Unix-domain that pstgres was built with by adding the 'host' keyword. create_engine("postgresql+psycopg2://user:pass@/dbname?host=/var/lib/pg")