-
-
Save rajivmehtaflex/8e225bc0235cc1cf49a6fcfff2bcff9f to your computer and use it in GitHub Desktop.
Cheat Sheet For Busy Coders: SQLAlchemy #SQL #mysql
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #! /usr/bin/python | |
| import sqlalchemy as sa | |
| sa.__version__ # '0.9.4' for me | |
| # Sample DB connection string | |
| # engine = create_engine('postgresql://scott:tiger@localhost/mydatabase') | |
| # engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase') | |
| # engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase') | |
| # engine = create_engine('mysql://scott:tiger@localhost/foo') | |
| # engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo') | |
| # engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo') | |
| # engine = create_engine('mysql+oursql://scott:tiger@localhost/foo') | |
| ################################################## | |
| # 'traditional' database definition / manipulation by SQL | |
| import sqlalchemy as sa | |
| engine = sa.create_engine('sqlite:///sqlite.db', echo=True) | |
| conn = engine.connect() | |
| # you got OperationalError if SQL is incorrect or table exists | |
| result = conn.execute(""" | |
| CREATE TABLE user ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| first_name TEXT, | |
| last_name TEXT, | |
| job TEXT | |
| ); | |
| """) | |
| # result.rowcount will be 1 | |
| result = conn.execute(""" | |
| INSERT INTO user (first_name, last_name, job) values ('Kenial', 'Lee', 'SE'); | |
| """) | |
| # due to stupid sqlite3_excute API ... it can just run one statement per one call | |
| result = conn.execute(""" | |
| INSERT INTO user (first_name, last_name, job) values ('Jon', 'Skeet', 'Author'); | |
| """) | |
| result = conn.execute(""" | |
| SELECT * FROM user; | |
| """) | |
| print result.rowcount # shoule be 2, but -1. due to stupid sqlite3 API ... | |
| # Iterate them all | |
| row = result.fetchone() | |
| while row: | |
| print row | |
| row = result.fetchone() | |
| # Otherwise, you got [(1, u'Kenial', u'Lee', u'SE'), (2, u'Jon', u'Skeet', u'Author')] | |
| print result.fetchall() | |
| # result.rowcount will be 2 | |
| result = conn.execute(""" | |
| DELETE FROM user; | |
| """) | |
| conn.close() | |
| ###################################### | |
| # Semi - statement mapping ways | |
| import sqlalchemy as sa | |
| engine = sa.create_engine('sqlite:///sqlite.db', echo=True) | |
| metadata = sa.MetaData(bind=engine) | |
| users = sa.Table( | |
| 'user', | |
| metadata, | |
| sa.Column('id', sa.Integer, primary_key=True, autoincrement=True), | |
| sa.Column('first_name', sa.String), | |
| sa.Column('last_name', sa.String), | |
| sa.Column('job', sa.String), | |
| ) | |
| metadata.create_all() # create table - it's safe to run many times | |
| # insert(), select() methods return Insert, Class object, which represents | |
| # SQL statement template and bahaviors like Command, Template class of | |
| # traditional DB library. | |
| users.insert(values={ | |
| "first_name": "Kenial", | |
| "last_name": "Lee", | |
| "job": "SE", | |
| }).execute() | |
| result = users.select().execute() | |
| # you got [(1, u'Kenial', u'Lee', u'SE')] | |
| print result.fetchall() | |
| ###################################### | |
| # ORM ways here | |
| import sqlalchemy as sa | |
| import sqlalchemy.orm as orm | |
| from sqlalchemy.ext.declarative import declarative_base | |
| engine = sa.create_engine('sqlite:///sqlite.db', echo=True) | |
| metadata = sa.MetaData(bind=engine) | |
| db_session = orm.scoped_session(orm.sessionmaker( | |
| autocommit=False, | |
| autoflush=False, | |
| bind=engine | |
| )) | |
| class User(object): | |
| id = sa.Column(sa.Integer, primary_key=True) | |
| first_name = sa.Column(sa.String) | |
| last_name = sa.Column(sa.String) | |
| job = sa.Column(sa.String) | |
| # | |
| def __init__(self, first_name=None, last_name=None, job=None): | |
| self.first_name = first_name | |
| self.last_name = last_name | |
| self.job = job | |
| # | |
| def __repr__(self): | |
| return '<User %r>' % (self.name) | |
| # | |
| query = sa.Table( | |
| 'user', | |
| metadata, | |
| sa.Column('id', sa.Integer, primary_key=True, autoincrement=True), | |
| sa.Column('first_name', sa.String), | |
| sa.Column('last_name', sa.String), | |
| sa.Column('job', sa.String), | |
| ) | |
| def init_db(): | |
| metadata.create_all(bind=engine) | |
| # set up db and schema | |
| init_db() | |
| orm.mapper(User, User.query) | |
| u = User("Kenial", "Lee", "SE") | |
| db_session.add(User("Kenial", "Lee", "SE")) | |
| db_session.add_all([ | |
| User("Kenial", "Lee", "SE"), | |
| User("Jon", "Skeet", "Author"), | |
| ]) | |
| db_session.commit() | |
| result = User.query.select(User.id == 1).execute() | |
| # you got [(1, u'Kenial', u'Lee', u'SE')] | |
| print result.fetchall() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment