Raw SQL
SqlSoup works fine with SQLAlchemy's text block support.
You can also access the SqlSoup's engine attribute to compose SQL directly. The engine's execute method corresponds to the one of a DBAPI cursor, and returns aResultProxy that has fetch methods you would also see on a cursor:
>>> rp = db.bind.execute('select name, email from users order by name') >>> for name, email in rp.fetchall(): print name, email Bhargan Basepair basepair+nospam@example.edu Joe Student student@example.edu
The docs for SqlSoup in the 0.3 branch are preserved at SqlSoup03. This page describes the 0.4 branch.
Introduction
SqlSoup provides a convenient way to access database tables without having to declare table or mapper classes ahead of time.
Suppose we have a database with users, books, and loans tables (corresponding to the PyWebOff dataset, if you're curious). For testing purposes, we'll create this db as follows:
>>> from sqlalchemy import create_engine
>>> e = create_engine('sqlite:///:memory:')
>>> for sql in _testsql: e.execute(sql) #doctest: +ELLIPSIS
<...
Creating a SqlSoup gateway is just like creating an SQLAlchemy engine:
>>> from sqlalchemy.ext.sqlsoup import SqlSoup >>> db = SqlSoup('sqlite:///:memory:')
or, you can re-use an existing metadata or engine:
>>> db = SqlSoup(MetaData(e))
You can optionally specify a schema within the database for your SqlSoup:
# >>> db.schema = myschemaname
bellow link is pretty good
http://mapfish.org/doc/tutorials/sqlalchemy.html
Executing
The interesting part of an Insert
is executing it. In this tutorial, we will generally focus on the most explicit method of executing a SQL construct, and later touch upon some "shortcut" ways to do it. The engine
object we created is a repository for database connections capable of issuing SQL to the database. To acquire a connection, we use the connect()
method:
>>> conn = engine.connect() >>> conn <sqlalchemy.engine.base.Connection object at 0x...>
The Connection
object represents an actively checked out DBAPI connection resource. Lets feed it our Insert
object and see what happens:
>>> result = conn.execute(ins)
Updates
Finally, we're back to UPDATE. Updates work a lot like INSERTS, except there is an additional WHERE clause that can be specified.
>>> # change 'jack' to 'ed' SQL>>> conn.execute(users.update(users.c.name=='jack'), name='ed')
['ed', 'jack']
COMMIT
<sqlalchemy.engine.base.ResultProxy object at 0x...> >>> # use bind parameters >>> u = users.update(users.c.name==bindparam('oldname'), values={'name':bindparam('newname')}) SQL>>> conn.execute(u, oldname='jack', newname='ed')
<sqlalchemy.engine.base.ResultProxy object at 0x...> >>> # update a column to an expression SQL>>> conn.execute(users.update(values={users.c.fullname:"Fullname: " + users.c.name}))
<sqlalchemy.engine.base.ResultProxy object at 0x...>