Pydbclib is Python Database Connectivity Lib, a general database operation toolkit for Python 3.6+
pip3 install pydbclibfrom pydbclib import connect
# 使用with上下文，可以自动提交，自动关闭连接
with connect("sqlite:///:memory:") as db:
db.execute('create table foo(a integer, b varchar(20))')
# 统一使用':[name]'形式的SQL的占位符
db.execute("insert into foo(a,b) values(:a,:b)", [{"a": 1, "b": "one"}]*4)
print(db.read("select * from foo").get_one())
print(db.read("select * from foo").get_all())
print(db.read("select * from foo").to_df())
# 对表常用操作的封装
table = db.get_table("foo")
table.insert([{"a": 2, "b": "two"}]*2) # 插入两条记录
table.find({"b": "two"}).get_all() # 查出b='two'的所有记录
table.update({"a": 2, "b": "two"}, {"b": "2"}) # 将a=2 and b='two'的所有记录的b字段值更新为'2'
table.find({"a": 2}).get_all() # 查出a=2的所有记录
table.delete({"a": 2}) # 删除a=2的所有记录Common Driver
# 使用普通数据库驱动连接，driver参数指定驱动包名称
# 例如pymysql包driver='pymysql',connect函数其余的参数和driver参数指定的包的创建连接参数一致
# 连接mysql
db = pydbclib.connect(user="user", password="password", database="test", driver="pymysql")
# 连接oracle
db = pydbclib.connect('user/password@local:1521/xe', driver="cx_Oracle")
# 通过odbc方式连接
db = pydbclib.connect('DSN=mysqldb;UID=user;PWD=password', driver="pyodbc")
# 通过已有驱动连接方式连接
import pymysql
con = pymysql.connect(user="user", password="password", database="test")
db = pydbclib.connect(driver=con)Sqlalchemy Driver
# 使用Sqlalchemy包来连接数据库，drvier参数默认为'sqlalchemy'
# 连接oracle
db = pydbclib.connect("oracle://user:password@local:1521/xe")
# 连接mysql
db = pydbclib.connect("mysql+pyodbc://:@mysqldb")
# 通过已有engine连接
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://user:password@localhost:3306/test")
db = pydbclib.connect(driver=engine)