rajivmehtaflex · June 12, 2022 03:51 · Aug 7, 2017 · Sep 17, 2016 · Sep 17, 2016 · Sep 17, 2016
diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -153,3 +153,54 @@ def init_db():
 
 # looks like ORM now!
 result = User.query.delete(User.id == 1).execute()
+
+
+
+##############################################################
+# "DB first" code
+
+from sqlalchemy.ext.automap import automap_base
+from sqlalchemy.orm import Session
+from sqlalchemy import create_engine
+from sqlalchemy.orm.query import Query
+from sqlalchemy import desc
+from sqlalchemy import text
+
+# engine, suppose it has two tables 'user' and 'address' set up
+engine = create_engine('mysql+pymysql://scott:tiger@localhost/dbname?charset=utf8')
+
+Base = automap_base()
+# reflect the tables
+Base.prepare(engine, reflect=True)
+
+# mapped classes are now created with names by default
+# matching that of the table name.
+SomeObj = Base.classes.some_table
+
+session = Session(engine)
+
+some_obj_list = session.query(SomeObj)[:10]
+some_obj_list = session.query(SomeObj.column_name).order_by(desc(SomeObj.id))[:10]
+
+
+session.query(SomeObj).filter(SomeObj.id.in_([10, 20]))
+session.query(SomeObj.id).filter_by(id=15)
+
+# rudimentary relationships are produced
+session.add(SomeObj(id=15, foreign_obj=ForeignObj(name="foo")))
+session.commit()
+
+
+# to check UPDATE, use session.dirty
+# to check INSERT, use session.new
+
+# bulk INSERT
+session.add_all([
+
+])
+
+
+# collection-based relationships are by default named
+# "<classname>_collection"
+some_obj = some_obj_list[0]
+print (u1.foreign_obj_collection)
diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -12,6 +12,7 @@
 # engine = sa.create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
 # engine = sa.create_engine('mysql://scott:tiger@localhost/foo?charset=utf8')
 # engine = sa.create_engine('mysql+mysqldb://scott:tiger@localhost/?charset=utf8')
+# engine = sa.create_engine('mysql+pymysql://scott:tiger@localhost/?charset=utf8')
 # engine = sa.create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')
 # engine = sa.create_engine('mysql+oursql://scott:tiger@localhost/foo')
 

diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -10,8 +10,8 @@
 # engine = sa.create_engine('postgresql://scott:tiger@localhost/mydatabase')
 # engine = sa.create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
 # engine = sa.create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
-# engine = sa.create_engine('mysql://scott:tiger@localhost/foo')
-# engine = sa.create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
+# engine = sa.create_engine('mysql://scott:tiger@localhost/foo?charset=utf8')
+# engine = sa.create_engine('mysql+mysqldb://scott:tiger@localhost/?charset=utf8')
 # engine = sa.create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')
 # engine = sa.create_engine('mysql+oursql://scott:tiger@localhost/foo')
 

diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -150,5 +150,5 @@ def init_db():
 # you got [(1, u'Kenial', u'Lee', u'SE')]
 print result.fetchall()
 
-# look more natural!
+# looks like ORM now!
 result = User.query.delete(User.id == 1).execute()
diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -89,6 +89,9 @@
 # you got [(1, u'Kenial', u'Lee', u'SE')]
 print result.fetchall()
 
+# Delete - should refer to c, which stands for 'column'
+users.delete(users.c.first_name == "Kenial").execute()
+
 ######################################
 # ORM ways here
 import sqlalchemy as sa
@@ -145,4 +148,7 @@ def init_db():
 
 result = User.query.select(User.id == 1).execute()
 # you got [(1, u'Kenial', u'Lee', u'SE')]
-print result.fetchall()
+print result.fetchall()
+
+# look more natural!
+result = User.query.delete(User.id == 1).execute()
diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -76,9 +76,9 @@
 )
 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. 
+# insert(), select() methods return Insert, Select class object, which 
+# represents SQL statement template and behaves like Command, Template 
+# class of traditional DB library. 
 users.insert(values={
     "first_name": "Kenial",
     "last_name": "Lee",

diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -45,7 +45,7 @@
     SELECT * FROM user;
 """)
 
-print result.rowcount   # shoule be 2, but -1. due to stupid sqlite3 API ...
+print result.rowcount   # should be 2, but returns -1. due to stupid sqlite3 API ...
 
 # Iterate them all
 row = result.fetchone()

diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -7,13 +7,13 @@
 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')
+# engine = sa.create_engine('postgresql://scott:tiger@localhost/mydatabase')
+# engine = sa.create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')
+# engine = sa.create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')
+# engine = sa.create_engine('mysql://scott:tiger@localhost/foo')
+# engine = sa.create_engine('mysql+mysqldb://scott:tiger@localhost/foo')
+# engine = sa.create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')
+# engine = sa.create_engine('mysql+oursql://scott:tiger@localhost/foo')
 
 
 ##################################################

diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -1,4 +1,8 @@
 #! /usr/bin/python
+
+# 02/27/2015, Kenial
+# Just simple scripts for instant use of SQLAlchemy.
+
 import sqlalchemy as sa
 sa.__version__  # '0.9.4' for me
 

diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -65,13 +65,16 @@
 users = sa.Table(
     'user', 
     metadata,
-    sa.Column('id', sa.Integer,  primary_key=True, autoincrement=True),
+    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",
@@ -113,7 +116,7 @@ def __repr__(self):
     query = sa.Table(
         'user', 
         metadata,
-        sa.Column('id', sa.Integer,  primary_key=True, autoincrement=True),
+        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),

diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -123,6 +123,7 @@ def __repr__(self):
 def init_db():
     metadata.create_all(bind=engine)
 
+
 # set up db and schema
 init_db()
 orm.mapper(User, User.query)

diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -119,6 +119,7 @@ def __repr__(self):
         sa.Column('job', sa.String),
     )
 
+
 def init_db():
     metadata.create_all(bind=engine)
 
@@ -134,6 +135,6 @@ def init_db():
 ])
 db_session.commit()
 
-result = User.query.select(id=1).execute()
+result = User.query.select(User.id == 1).execute()
 # you got [(1, u'Kenial', u'Lee', u'SE')]
 print result.fetchall()
diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -70,7 +70,7 @@
     sa.Column('last_name', sa.String),
     sa.Column('job', sa.String),
 )
-metadata.create_all()     # it's safe to run many times
+metadata.create_all()     # create table - it's safe to run many times
 
 users.insert(values={
     "first_name": "Kenial",
@@ -79,6 +79,8 @@
 }).execute()
 result = users.select().execute()
 
+# you got [(1, u'Kenial', u'Lee', u'SE')]
+print result.fetchall()
 
 ######################################
 # ORM ways here
@@ -108,7 +110,7 @@ def __init__(self, first_name=None, last_name=None, job=None):
     def __repr__(self):
         return '<User %r>' % (self.name)
     #
-    _table = sa.Table(
+    query = sa.Table(
         'user', 
         metadata,
         sa.Column('id', sa.Integer,  primary_key=True, autoincrement=True),
@@ -122,7 +124,7 @@ def init_db():
 
 # set up db and schema
 init_db()
-orm.mapper(User, User._table)
+orm.mapper(User, User.query)
 
 u = User("Kenial", "Lee", "SE")
 db_session.add(User("Kenial", "Lee", "SE"))
@@ -131,3 +133,7 @@ def init_db():
     User("Jon", "Skeet", "Author"),
 ])
 db_session.commit()
+
+result = User.query.select(id=1).execute()
+# you got [(1, u'Kenial', u'Lee', u'SE')]
+print result.fetchall()
diff --git a/csfbc_sqlalchemy.py b/csfbc_sqlalchemy.py
@@ -0,0 +1,133 @@
+#! /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()     # it's safe to run many times
+
+users.insert(values={
+    "first_name": "Kenial",
+    "last_name": "Lee",
+    "job": "SE",
+}).execute()
+result = users.select().execute()
+
+
+######################################
+# 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)
+    #
+    _table = 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._table)
+
+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()