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)

  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

PostgreSQL

psycopg2

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")