Python：SQLAlchemy 2.0 库使用教程

SQLAlchemy 2.0

版本检查

import sqlalchemy
sqlalchemy.__version__  

建立连接-engine

任何 SQLAlchemy 应用程序的开始都是一个称为Engine的对象。此对象充当连接到特定数据库的中心源，既提供一个工厂，又提供一个称为 连接池 的存储空间，用于这些数据库连接。

用法：

engine = create_engine('dialect+driver://username:password@host:port/database')

• dialect：数据库类型
• driver：数据库驱动选择
• username：数据库用户名
• password： 用户密码
• host：服务器地址
• port：端口
• database：数据库

SQLAlchemy 通过 Engine 管理与数据库的连接信息。

from sqlalchemy import create_engine,text,Table,MetaData

# 1. 创建引擎（mysql数据库引擎）
engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3306/my_db', echo=True)

# 2. 连接数据库
with engine.connect() as conn:
    result = conn.execute(text('select * from student'))
    print(result.fetchall())
    conn.execute(text('insert into student (uid, name, age) values (:uid,:name,:age)'),{'uid':106,'name':'xiaoba','age':22})
    conn.commit()
    result2 = conn.execute(text('select * from student'))
    print(result2.fetchall())
    
    # 传入多个参数，一次性插入多行数据
    conn.execute(text('insert into student (uid, name, age) values (:uid,:name,:age)'),[{'uid':106,'name':'xiaoba','age':22},{'uid':107,'name':'laojiu','age':25},{'uid':108,'name':'chengshi'}])
    conn.close()

创建引擎的 echo=True 参数代表在控制台上打印运行SQL日志；在这个例子中，我们使用 text() 函数构造了一个安全的 SQL 语句，并通过字典传递了参数；通过 fetchall() 获取执行SQL返回的结果集，值得注意的是，再调用该方法后，会关闭结果集，再次调用只会返回空列表(源码中有讲解)；整个execute执行语句是隐式包含事务的，如果不通过 commit() 进行提交，默认会 ROLLBACK 回滚。

下面展示下连接不同数据库驱动的选择：

PostgreSQL

engine = create_engine('postgresql://scott:tiger@localhost/mydatabase')

psycopg2

engine = create_engine('postgresql+psycopg2://scott:tiger@localhost/mydatabase')

pg8000

engine = create_engine('postgresql+pg8000://scott:tiger@localhost/mydatabase')

MySQL

default
engine = create_engine('mysql://scott:tiger@localhost/foo')

mysql-python

engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')

MySQL-connector-python

engine = create_engine('mysql+mysqlconnector://scott:tiger@localhost/foo')

OurSQL

engine = create_engine('mysql+oursql://scott:tiger@localhost/foo')

More notes on connecting to MySQL at MySQL.

Oracle

engine = create_engine('oracle://scott:tiger@127.0.0.1:1521/sidname')
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname')

More notes on connecting to Oracle at Oracle.

Microsoft SQL Server

engine = create_engine('mssql+pyodbc://scott:tiger@mydsn')

pymssql

engine = create_engine('mssql+pymssql://scott:tiger@hostname:port/dbname')

SQLite

engine = create_engine('sqlite:///foo.db')
engine = create_engine('sqlite:absolute/path/to/foo.db')

使用

engine = create_engine('mysql+mysqlconnector://root:123456@localhost:3306/my_db', echo=True)

• echo=True代表在控制台打印SQL执行过程

注意：这里的创建引擎并不代表创建了数据库的连接器。

Pandas处理的数据写入数据库

from sqlalchemy import create_engine,text,Table,MetaData
import pandas pd

# pandas 加载处理数据
...

# 可选：检查DataFrame列是否与数据库表列匹配
metadata = MetaData()
table = Table(DB_TABLE, metadata, autoload_with=engine)
df_cols = set(df.columns)
table_cols = set(table.columns.keys())

if df_cols != table_cols:
    print("DataFrame columns and database table columns do not match:")
    print(f"DataFrame columns: {df_cols}")
    print(f"Table columns: {table_cols}")
    # 在这里，您可以选择添加或删除列，以使它们匹配
    # 或者，您可以修改数据库表结构以匹配DataFrame
else:
    print("DataFrame columns match database table columns.")

# 将DataFrame插入到MySQL表中
df.to_sql(DB_TABLE, con=engine, if_exists='append', index=False)