Direct SQL Query

Database Connection

import sqlalchemy as sa
engine_uri = 'sqlite:///db.sql3'
engine = sa.create_engine(engine_uri)

the engine_uri has quite some slashes. The "://" part is the seperator. "/db.sql3" is a relative path. Absolute paths start with two slashes "//tmp/folder/db.sql3"

Query

Just executing our query. First we define it. Then we execute it. We now have a resultproxy. And finally we just fetch all the records.

>>> sql = sa.text('select * from users')
>>> result = engine.execute(sql)
>>> print result.fetchall()
[(1, u'ed', u'Ed Jones', u'edspassword'), 
(2, u'wendy', u'Wendy Williams', u'foobar'), 
(3, u'mary', u'Mary Contrary', u'xxg527'), 
(4, u'fred', u'Fred Flinstone', u'blah')]

The resultproxy can be sliced, iterated, or just fetched one by one.

>>>for row in engine.execute(sa.text('select id, name from users')):
...    print '%03d: %s' % tuple(row)
001: ed
002: wendy
003: mary
004: fred

What is inside a row?

>>> row = engine.execute(sa.text('select * from users')).fetchone()
>>> print row
(1, u'ed', u'Ed Jones', u'edspassword')

The row knows the column names

>>> print dict(row)
{u'fullname': u'Ed Jones', u'password': u'edspassword', 
u'id': 1, u'name': u'ed'}
>>> print row['fullname']
Ed Jones