pydbal 0.12

查询语句

要选择数据库中的数据，可以使用query方法。这个 方法将返回pydbal.statement.Statement的实例。

# simple fetch generatorforrowinconn.query('SELECT * FROM table'):print(row)# same as the above but fetch mode can be applied (Connection.FETCH_*)forrowinconn.query('SELECT * FROM table').iterate(fetch_mode=Connection.FETCH_OBJECT):print(row)# fetch row by rowresult=conn.query('SELECT * FROM table')row1=result.fetch()row2=result.fetch()# fetch all rowsrows=conn.query('SELECT * FROM table').fetch_all()# fetch single value from columncount=conn.query('SELECT COUNT(*) FROM table').fetch_column()# fetch all values from column by indexids=conn.query('SELECT id FROM table').fetch_all(fetch_mode=Connection.FETCH_COLUMN,column_index=0)

执行语句

要执行insert、update或delete语句，可以使用 execute方法。此方法将返回受影响的行数。

# INSERTconn.execute('INSERT INTO table VALUES (?)',[val1,val2,val3])last_insert_id=conn.last_insert_id()# UPDATEaffected_rows=conn.execute('UPDATE table SET column = ? WHERE id = ?',val1,id_)# DELETEaffected_rows=conn.execute('DELETE FROM table WHERE id = ?',id_)

语句参数

query和execute方法都支持通过 在第一个sql参数后传递参数。

# single positional parameterrow=conn.query('SELECT * FROM table WHERE id = ?',id_).fetch()# multiple positional parametersrow=conn.query('SELECT * FROM table WHERE id = ? OR id = ?',id1,id2).fetch()# named parametersrow=conn.query('SELECT * FROM table WHERE id = :id1 OR id = :id2',id1=id1,id2=id2).fetch()# iterable parametersrow=conn.query('SELECT * FROM table WHERE id IN (?)',[id1,id2]).fetch()

交易

pydbal支持事务操作。

conn.begin_transaction()try:# ... execute statements ...conn.commit()returnsmthexcept:conn.rollback()raise# same as the abovedeftrans(conn):# ... execute statements ...returnsmthsmth=conn.transaction(trans)

如果数据库平台支持savepoints则可以启用和使用嵌套 交易。

conn.set_nest_transactions_with_savepoints(True)conn.begin_transaction()# ... execute statements 1 ...conn.begin_transaction()# ... execute statements 2 ...conn.commit()# commit 1conn.rollback()# rollback 2# to control savepoints manuallyconn.create_savepoint('MYSAVEPOINT')conn.release_savepoint('MYSAVEPOINT')conn.rollback_savepoint('MYSAVEPOINT')

SQL生成器

使编写SQL语句更加简单灵活 使用pydbal.builder.SQLBuilder。

# SELECTsqb=(conn.sql_builder().select('t1.id','t2.id','SUM(t1.col) AS special').from_('table1','t1').join('t1','table2','t2','t2.id = t1.id').where('t1.col = :val').set_parameter('val',val).group_by('t1.col').having('special IS NOT NULL').order_by('t2.id'))forrowinsqb.execute():print(row)# INSERTlast_insert_id=(conn.sql_builder().insert('table').values({'col1':val1,'col2':val2})).execute()# UPDATEaffected_rows=(conn.sql_builder().update('table').set('col1',val1).set('col2',val2).where('id = :id').set_parameter('id',id_)).execute()# DELETEaffected_rows=(conn.sql_builder().delete('table').where('id = ?').set_parameter(0,id_)).execute()

表达式生成器

^可以创建{tt18}$、HAVING和JOIN ... ON表达式 使用pydbal.builder.ExpressionBuilder。

expr=conn.get_expression_builder()# or via SQL Builder instance# expr = sqb.expr()sqb.where(expr.and_x(expr.eq('a','b'),expr.is_null('c')).or_x(expr.and_x('d IS NULL',expr.in_('e',['1','2','3'])),expr.neq('f',expr.literal('abc'))))

模式管理器

pydbal附带了简单的只读sql模式管理器。它支持 数据库、表、视图、列、索引和外键的列表。 使用pydbal.cache机制缓存内部数据库查询。

sm=conn.get_schema_manager()# database namesdb_names=sm.get_database_names()# viewsviews=sm.get_views()view_names=sm.get_view_names()# tablestables=sm.get_tables()table_names=sm.get_table_names()# columnstable_columns=sm.get_table_columns('table')table_column_names=sm.get_table_column_names('table')# indexestable_indexes=sm.get_table_indexes('table')table_index_names=sm.get_table_index_names('table')# foreign keystable_foreign_keys=sm.get_table_foreign_keys('table')table_foreign_key_names=sm.get_table_foreign_key_names('table')

螺纹安全连接

Pydbal v0.10+支持在 pydbal.threading模块。

frompydbal.threadingimportSafeConnectionconn=SafeConnection('mysql',host='localhost',user='root',database='mydb')

SafeConnectionwrapper类在锁定池中维护活动连接 并提供用于操作数据的帮助器方法。类实现方法 locked()，应该传递给with语句。它产生 独立连接上下文，可用于发送非琐碎命令 到原始的pydbal.connection.Connection对象。

# simple fetch generatorforrowinconn.query('SELECT * FROM table'):print(row)# fetch one rowrow=conn.fetch('SELECT * FROM table WHERE id = ?',id_)# fetch all rowsrows=conn.fetch_all('SELECT * FROM table')# fetch single value from columncount=conn.fetch_column('SELECT COUNT(*) FROM table')# UPDATE or DELETE queriesaffected_rows=conn.execute('UPDATE table SET column = ? WHERE id = ?',val1,id_)# INSERT query with last inserted IDwithconn.locked()as_conn:_conn.execute('INSERT INTO table VALUES (?)',[val1,val2,val3])last_insert_id=_conn.last_insert_id()# transaction callbackdeftrans(_conn):# ... execute statements ...returnsmthsmth=conn.transaction(trans)