pymysql-manager 0.0.2

新功能

1. 参数“charset”默认为utf8
2. 参数“autocommit”默认值为true
3. 添加了参数“时区”，默认值为“+00:00”
4. 默认情况下使用pymysql.cursors.dictcursor
5. 数据库连接丢失后重新连接
6. 添加用于创建连接、mysql警告、异常、数据库查询等的日志。
7. 使用with…作为事务操作的语法
8. 提供简化的查询方法，例如fetch_all/fetch_row/fetch_column/fetch_first
9. 提供简单的方法，如插入/插入多个/更新/删除

一。创建pymysql连接

importpymysqlfrompymysql_managerimportConnectionconn=Connection(host='192.0.0.1',database='foo',timezone='+8:00')

2.交易

编码前：

try:conn.begin()conn.execute(....)catchException:conn.rollback()else:conn.commit()

现在：

withconn.transaction():conn.execute(...)

三。获取行集

# executed: select * from foo where id between 5 and 10all_rows=conn.fetch_all('select * from foo where id between %s and %s',5,10)# executed: select * from foo limit 1first_row=conn.fetch_row('select * from foo')# executed: select * from foo limit 1first_column_on_first_row=conn.fetch_first('select * from foo')# executed: select * from foo limit 1third_column_on_first_row=conn.fetch_column('select * from foo',column=3)

四。通过迭代器获取

当结果很大时，可以使用sscursor。但有时使用limit…offset…可以减少对数据库的压力

作者：sscursor

cursor=conn.cursor(pymysql.cursors.SSCursor)conn.execute(sql)whileTrue:row=cursor.fetchone()ifnotrow:break

通过获取迭代器

forrowinconn.fetch_iterator(sql,per=1000,max=100000):print(row)

5个。单个/批量插入或替换更新删除

# insert ignore into mytable (foo, bar) values (1, 2)db.insert('insert ignore into mytable',foo=1,bar=2)# insert ignore into mytable (foo, bar) values (1, 2) on duplicate key update ...db.insert('insert ignore into mytable on duplicate key update ...',**dict(foo=1,bar=2))# insert ignore into mytable (id, name) values (1, 'foo'), (2, 'bar') on duplicate key update ...db.insert_many('insert ignore into mytable on duplicate key update ...',['id','name'],[(1,'foo'),(2,'bar')])# update mytable set foo=1, bar=2 where id between %s and %sdb.update('update mytable where id between %s and %s',10,5,foo=1,bar=2)db.update('update mytable where id between %s and %s',[10,5],foo=1,bar=2)db.update('update mytable where id between %s and %s',*[10,5],**dict(foo=1,bar=2))# update from mytable where id between %s and %sdb.delete('delete from mytable id between %s and %s',10,5)db.delete('delete from mytable id between %s and %s',[10,5])

一。创建连接池

frompymysql_managerimportConnectionPooledpooled=ConnectionPooled(host='192.0.0.1',database='foo',pool_options=dict(max_size=10,max_usage=100000,idle=60,ttl=120))

2.在没有连接池的情况下执行sql

pooled.execute(sql)pooled.connection.execute(sql)

三。使用连接池执行sql

withpooled.pool()asconnection:connection.execute(sql)

一。配置

frompymysql_managerimportConnectionManagerm=ConnectionManager(default='foo',foo=dict(host='192.0.0.1',database='foo',user='root',passwd=''),bar=dict(host='192.0.0.1',database='bar',user='root',passwd=''))

2.连接

m.execute(sql)# use default connectionm['foo].execute(sql)m.connection('foo').exeucte(sql)

三。从连接池获取连接

withm.pool()asconnection:pass# use default connectionwithm['foo'].pool()asconnection:passwithm.connection('foo').pool()asconnection:pass