This is a little experiment I created with SQLAlchemy. In this notebook, I'm using sqlite to create a table, and doing some operations such as deleting all the rows in the table and inserting a list of items.

In [2]:
# connection is a connection to the database from a pool of connections
connection = engine.connect()
# meta will be used to reflect the table later
meta = MetaData()
2014-08-10 21:10:16,410 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1

INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1

2014-08-10 21:10:16,411 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2014-08-10 21:10:16,413 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1

INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1

2014-08-10 21:10:16,414 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

In [3]:
# create the table if it doesn't exist already
connection.execute("create table if not exists test ( id integer primary key autoincrement, name text )")
2014-08-10 21:10:17,212 INFO sqlalchemy.engine.base.Engine create table if not exists test ( id integer primary key autoincrement, name text )

INFO:sqlalchemy.engine.base.Engine:create table if not exists test ( id integer primary key autoincrement, name text )

2014-08-10 21:10:17,214 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2014-08-10 21:10:17,217 INFO sqlalchemy.engine.base.Engine COMMIT

INFO:sqlalchemy.engine.base.Engine:COMMIT

Out[3]:
<sqlalchemy.engine.result.ResultProxy at 0x105083e48>
In [4]:
#reflects all the tables in the current connection
meta.reflect(bind=engine)
2014-08-10 21:10:17,986 INFO sqlalchemy.engine.base.Engine SELECT name FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE type='table' ORDER BY name

INFO:sqlalchemy.engine.base.Engine:SELECT name FROM  (SELECT * FROM sqlite_master UNION ALL   SELECT * FROM sqlite_temp_master) WHERE type='table' ORDER BY name

2014-08-10 21:10:17,987 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2014-08-10 21:10:17,989 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("sqlite_sequence")

INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("sqlite_sequence")

2014-08-10 21:10:17,990 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2014-08-10 21:10:17,993 INFO sqlalchemy.engine.base.Engine PRAGMA foreign_key_list("sqlite_sequence")

INFO:sqlalchemy.engine.base.Engine:PRAGMA foreign_key_list("sqlite_sequence")

2014-08-10 21:10:17,995 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2014-08-10 21:10:17,997 INFO sqlalchemy.engine.base.Engine PRAGMA index_list("sqlite_sequence")

INFO:sqlalchemy.engine.base.Engine:PRAGMA index_list("sqlite_sequence")

2014-08-10 21:10:17,997 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2014-08-10 21:10:17,999 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("test")

INFO:sqlalchemy.engine.base.Engine:PRAGMA table_info("test")

2014-08-10 21:10:18,000 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2014-08-10 21:10:18,001 INFO sqlalchemy.engine.base.Engine PRAGMA foreign_key_list("test")

INFO:sqlalchemy.engine.base.Engine:PRAGMA foreign_key_list("test")

2014-08-10 21:10:18,002 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2014-08-10 21:10:18,004 INFO sqlalchemy.engine.base.Engine PRAGMA index_list("test")

INFO:sqlalchemy.engine.base.Engine:PRAGMA index_list("test")

2014-08-10 21:10:18,005 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

In [5]:
# grabs a Table object from meta
test = meta.tables['test']
test
Out[5]:
Table('test', MetaData(bind=None), Column('id', INTEGER(), table=<test>, primary_key=True, nullable=False), Column('name', TEXT(), table=<test>), schema=None)
In [6]:
# cleans out all the rows in the test table
result = connection.execute(test.delete())
print("Deleted %d row(s)" % result.rowcount)
2014-08-10 21:10:22,659 INFO sqlalchemy.engine.base.Engine DELETE FROM test

INFO:sqlalchemy.engine.base.Engine:DELETE FROM test

2014-08-10 21:10:22,661 INFO sqlalchemy.engine.base.Engine ()

INFO:sqlalchemy.engine.base.Engine:()

2014-08-10 21:10:22,662 INFO sqlalchemy.engine.base.Engine COMMIT

INFO:sqlalchemy.engine.base.Engine:COMMIT

Deleted 11 row(s)

In [7]:
# create a list of names to be inserting into the test table
names = ['alpha', 'bravo', 'charlie', 'delta', 'epsilon', 'foxtrot', 'golf', 'hotel', 'india', 'juliet', 'lima']
In [8]:
# perform multiple inserts, the list is converted on the fly into a dictionary with the name field.
result = connection.execute(test.insert(), [{'name': name} for name in names])
print("Inserted %d row(s)" % result.rowcount)
2014-08-10 21:10:26,580 INFO sqlalchemy.engine.base.Engine INSERT INTO test (name) VALUES (?)

INFO:sqlalchemy.engine.base.Engine:INSERT INTO test (name) VALUES (?)

2014-08-10 21:10:26,582 INFO sqlalchemy.engine.base.Engine (('alpha',), ('bravo',), ('charlie',), ('delta',), ('epsilon',), ('foxtrot',), ('golf',), ('hotel',)  ... displaying 10 of 11 total bound parameter sets ...  ('juliet',), ('lima',))

INFO:sqlalchemy.engine.base.Engine:(('alpha',), ('bravo',), ('charlie',), ('delta',), ('epsilon',), ('foxtrot',), ('golf',), ('hotel',)  ... displaying 10 of 11 total bound parameter sets ...  ('juliet',), ('lima',))

2014-08-10 21:10:26,583 INFO sqlalchemy.engine.base.Engine COMMIT

INFO:sqlalchemy.engine.base.Engine:COMMIT

Inserted 11 row(s)

In [9]:
# query the rows with select, the where clause is included for demostration
# it can be omitted
result = connection.execute(select([test]).where(test.c.id > 0)) 
2014-08-10 21:10:28,528 INFO sqlalchemy.engine.base.Engine SELECT test.id, test.name 
FROM test 
WHERE test.id > ?

INFO:sqlalchemy.engine.base.Engine:SELECT test.id, test.name 
FROM test 
WHERE test.id > ?

2014-08-10 21:10:28,529 INFO sqlalchemy.engine.base.Engine (0,)

INFO:sqlalchemy.engine.base.Engine:(0,)

In [10]:
# show the results
for row in result:
    print("id=%d, name=%s" % (row['id'], row['name']))
id=56, name=alpha
id=57, name=bravo
id=58, name=charlie
id=59, name=delta
id=60, name=epsilon
id=61, name=foxtrot
id=62, name=golf
id=63, name=hotel
id=64, name=india
id=65, name=juliet
id=66, name=lima

In []:


Comments

comments powered by Disqus