python sqlalchemy执行原始sql语句示例

参考文档

官方文档首页: http://docs.sqlalchemy.org/en/latest/

连接数据库

这边使用sqlite的内存数据库，方便测试

import sqlalchemy

db_engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
db_conn = db_engine.connect()

使用create_engine可以建立一个连接池，调用connect可以从连接池中获取一个连接,调用db_conn.close()会把连接释放给连接池,并做相应的清理工作。

官方说明：http://docs.sqlalchemy.org/en/latest/core/connections.html#basic-usage

The connection is an instance of Connection, which is a proxy object for an actual DBAPI connection. The DBAPI connection is retrieved from the connection pool at the point at which Connection is created.

The returned result is an instance of ResultProxy, which references a DBAPI cursor and provides a largely compatible interface with that of the DBAPI cursor. The DBAPI cursor will be closed by the ResultProxy when all of its result rows (if any) are exhausted. A ResultProxy that returns no rows, such as that of an UPDATE statement (without any returned rows), releases cursor resources immediately upon construction.

When the close() method is called, the referenced DBAPI connection is released to the connection pool. From the perspective of the database itself, nothing is actually “closed”, assuming pooling is in use. The pooling mechanism issues a rollback() call on the DBAPI connection so that any transactional state or locks are removed, and the connection is ready for its next usage.

使用原始SQL

先进行建表操作

db_conn.execute(r'''
CREATE TABLE IF NOT EXISTS stocks (
        date text, 
        trans text, 
        symbol text, 
        qty real, 
        pricereal
    )
''')

插入单条数据

直接拼装一个完整的sql字符串，或使用下面防注入的参数绑定方式, 注意:会自动commit

db_conn.execute(r'''
INSERT INTO stocks VALUES (?, ?, ?, ?, ?)
''', ('2001-01-11', 'BUY', 'RHAT', 100, 35.14) )

db_conn.execute(r'''
INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)
''', ('2001-01-11', 'BUY', 'RHAT', 100, 35.14) )

操作日志显示会自动commit

2017-08-27 15:05:26,834 INFO sqlalchemy.engine.base.Engine ()
2017-08-27 15:05:26,834 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-27 15:05:26,834 INFO sqlalchemy.engine.base.Engine 
INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)

2017-08-27 15:05:26,834 INFO sqlalchemy.engine.base.Engine ('2001-01-11', 'BUY', 'RHAT', 100, 35.14)
2017-08-27 15:05:26,834 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-27 15:05:26,835 INFO sqlalchemy.engine.base.Engine 
INSERT INTO stocks VALUES (?, ?, ?, ?, ?)

2017-08-27 15:05:26,835 INFO sqlalchemy.engine.base.Engine ('2001-01-11', 'BUY', 'RHAT', 100, 35.14)
2017-08-27 15:05:26,835 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-27 15:05:26,835 INFO sqlalchemy.engine.base.Engine 
INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)

插入多条数据

与插入单条数据类似, 注意:会自动commit

purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
            ]
db_conn.execute(r'''
INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)
''', purchases)
db_conn.execute(r'''
INSERT INTO stocks VALUES (?, ?, ?, ?, ?)
''', purchases)

操作日志显示会自动commit

2017-08-27 15:05:26,835 INFO sqlalchemy.engine.base.Engine 
INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)

2017-08-27 15:05:26,835 INFO sqlalchemy.engine.base.Engine [('2006-03-28', 'BUY', 'IBM', 1000, 45.0), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0), ('2006-04-06', 'SELL', 'IBM', 500, 53.0)]
2017-08-27 15:05:26,835 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-27 15:05:26,835 INFO sqlalchemy.engine.base.Engine 
INSERT INTO stocks VALUES (?, ?, ?, ?, ?)

2017-08-27 15:05:26,835 INFO sqlalchemy.engine.base.Engine [('2006-03-28', 'BUY', 'IBM', 1000, 45.0), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0), ('2006-04-06', 'SELL', 'IBM', 500, 53.0)]
2017-08-27 15:05:26,835 INFO sqlalchemy.engine.base.Engine COMMIT

使用事务处理

参考文档: http://docs.sqlalchemy.org/en/latest/core/connections.html#using-transactions

由于上面的插入操作会自动进行commit，sqlalchemy提供了Transactions来管理commit和rollback。

完整的测试代码如下

import sqlalchemy

db_engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True, pool_size=2)
db_conn = db_engine.connect()

db_conn.execute(r'''
CREATE TABLE IF NOT EXISTS stocks (date text, trans text, symbol text, qty real, price real)
''')

with db_conn.begin() as db_trans:
    #
    db_conn.execute(r'''
    INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)
    ''', ('aa', 'BUY', 'RHAT', 100, 35.14) )

    db_conn.execute(r'''
    INSERT INTO stocks VALUES (?, ?, ?, ?, ?)
    ''', ('bb', 'BUY', 'RHAT', 100, 35.14) )

    #提前进行commit
    db_trans.commit()

    purchases = [('cc', 'BUY', 'IBM', 1000, 45.00),
                 ('dd', 'BUY', 'MSFT', 1000, 72.00),
                 ('ee', 'SELL', 'IBM', 500, 53.00),
                ]
    db_conn.execute(r'''
    INSERT INTO stocks VALUES (?,?,?,?,?)
    ''', purchases)


query_result = db_conn.execute(r'''
SELECT * FROM  stocks
''').fetchall()
print('query_result:', query_result)

完整的日志如下

2017-08-27 15:18:22,057 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2017-08-27 15:18:22,057 INFO sqlalchemy.engine.base.Engine ()
2017-08-27 15:18:22,057 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2017-08-27 15:18:22,057 INFO sqlalchemy.engine.base.Engine ()
2017-08-27 15:18:22,058 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE IF NOT EXISTS stocks (date text, trans text, symbol text, qty real, price real)

2017-08-27 15:18:22,058 INFO sqlalchemy.engine.base.Engine ()
2017-08-27 15:18:22,058 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-27 15:18:22,058 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-08-27 15:18:22,058 INFO sqlalchemy.engine.base.Engine 
    INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)

2017-08-27 15:18:22,058 INFO sqlalchemy.engine.base.Engine ('aa', 'BUY', 'RHAT', 100, 35.14)
2017-08-27 15:18:22,058 INFO sqlalchemy.engine.base.Engine 
    INSERT INTO stocks VALUES (?, ?, ?, ?, ?)

2017-08-27 15:18:22,059 INFO sqlalchemy.engine.base.Engine ('bb', 'BUY', 'RHAT', 100, 35.14)
2017-08-27 15:18:22,059 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-27 15:18:22,059 INFO sqlalchemy.engine.base.Engine 
    INSERT INTO stocks VALUES (?,?,?,?,?)

2017-08-27 15:18:22,059 INFO sqlalchemy.engine.base.Engine [('cc', 'BUY', 'IBM', 1000, 45.0), ('dd', 'BUY', 'MSFT', 1000, 72.0), ('ee', 'SELL', 'IBM', 500, 53.0)]
2017-08-27 15:18:22,059 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-27 15:18:22,059 INFO sqlalchemy.engine.base.Engine 
SELECT * FROM  stocks

2017-08-27 15:18:22,059 INFO sqlalchemy.engine.base.Engine ()
query_result: [('aa', 'BUY', 'RHAT', 100.0, 35.14), ('bb', 'BUY', 'RHAT', 100.0, 35.14), ('cc', 'BUY', 'IBM', 1000.0, 45.0), ('dd', 'BUY', 'MSFT', 1000.0, 72.0), ('ee', 'SELL', 'IBM', 500.0, 53.0)]

可以看到有三次commit操作,有三次commit操作，一次是建表，两次是插入，其中第二次是显示调用commit,第三次是with结束后自动调用。因此如果想避免频繁的commit，可以使用with来进行上下文管理。

测试异常

import sqlalchemy

db_engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True, pool_size=2)
db_conn = db_engine.connect()

try:
    with db_conn.begin() as db_trans:
        db_conn.execute(r'''
        CREATE TABLE IF NOT EXISTS stocks (date text, trans text, symbol text, qty real, price real)
        ''')

        db_conn.execute(r'''
        INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)
        ''', ('aa', 'BUY', 'RHAT', 100, 35.14) )
        db_trans.commit()

        db_conn.execute(r'''
        INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)
        ''', ('bb', 'BUY', 'RHAT', 100, 35.14) )

        db_conn.execute(r'''
        INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)
        ''', ('cc', 'BUY', 'RHAT', 100) ) #这里故意少传一个参数来制造异常
except:
    print('exception!!!')


query_result = db_conn.execute(r'''
SELECT * FROM  stocks
''').fetchall()
print('query_result:', query_result)

日志

2017-08-27 15:40:53,151 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2017-08-27 15:40:53,151 INFO sqlalchemy.engine.base.Engine ()
2017-08-27 15:40:53,152 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2017-08-27 15:40:53,152 INFO sqlalchemy.engine.base.Engine ()
2017-08-27 15:40:53,152 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2017-08-27 15:40:53,152 INFO sqlalchemy.engine.base.Engine 
        CREATE TABLE IF NOT EXISTS stocks (date text, trans text, symbol text, qty real, price real)

2017-08-27 15:40:53,153 INFO sqlalchemy.engine.base.Engine ()
2017-08-27 15:40:53,153 INFO sqlalchemy.engine.base.Engine 
        INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)

2017-08-27 15:40:53,153 INFO sqlalchemy.engine.base.Engine ('aa', 'BUY', 'RHAT', 100, 35.14)
2017-08-27 15:40:53,153 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-27 15:40:53,153 INFO sqlalchemy.engine.base.Engine 
        INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)

2017-08-27 15:40:53,153 INFO sqlalchemy.engine.base.Engine ('bb', 'BUY', 'RHAT', 100, 35.14)
2017-08-27 15:40:53,153 INFO sqlalchemy.engine.base.Engine COMMIT
2017-08-27 15:40:53,153 INFO sqlalchemy.engine.base.Engine 
        INSERT INTO stocks VALUES (:1, :2, :3, :4, :5)

2017-08-27 15:40:53,153 INFO sqlalchemy.engine.base.Engine ('cc', 'BUY', 'RHAT', 100)
2017-08-27 15:40:53,154 INFO sqlalchemy.engine.base.Engine ROLLBACK
exception!!!
2017-08-27 15:40:53,154 INFO sqlalchemy.engine.base.Engine 
SELECT * FROM  stocks

2017-08-27 15:40:53,154 INFO sqlalchemy.engine.base.Engine ()
query_result: [('aa', 'BUY', 'RHAT', 100.0, 35.14), ('bb', 'BUY', 'RHAT', 100.0, 35.14)]