SQLAlchemy 第一篇

安装SQLAlchemy

pip install SQLAlchemy

查看当前版本

# 查看当前版本

import sqlalchemy

print(sqlalchemy.__version__)

2.0.23

创建数据库连接

此处我们以pymysql为mysql的数据库驱动

安装pymysql

pip install pymysql

from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:root@localhost/test?charset=utf8mb4",echo=True,echo_pool=True,pool_size=20)

执行原生的sql语句

from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(text("select 'Hello SQLAlchemy'"))
    print(result.all())

2023-12-14 10:18:36,836 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 10:18:36,837 INFO sqlalchemy.engine.Engine select 'Hello SQLAlchemy'
2023-12-14 10:18:36,838 INFO sqlalchemy.engine.Engine [generated in 0.00197s] {}
[('Hello SQLAlchemy',)]
2023-12-14 10:18:36,839 INFO sqlalchemy.engine.Engine ROLLBACK

创建表与执行插入语句

在下面的示例中，上下文管理器提供了数据库连接，并且还构建了事务内部的操作。Python DBAPI 的默认行为包括事务始终在进行中；当连接范围被释放时，会发出 ROLLBACK 来结束事务。事务不会自动提交；当我们想要提交数据时，我们通常需要调用Connection.commit()

with engine.connect() as conn:
    conn.execute(
        text("CREATE TABLE IF NOT EXISTS `user` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"))
    conn.execute(
        text("insert into user(name,age) values(:name,:age )"),
        [{"name":"cloud","age":18},{"name":"alex","age":19}]
    )
    conn.commit()

2023-12-14 10:56:16,027 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 10:56:16,029 INFO sqlalchemy.engine.Engine CREATE TABLE IF NOT EXISTS `user` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
2023-12-14 10:56:16,030 INFO sqlalchemy.engine.Engine [cached since 1825s ago] {}
2023-12-14 10:56:16,032 INFO sqlalchemy.engine.Engine insert into user(name,age) values(%(name)s,%(age)s )
2023-12-14 10:56:16,033 INFO sqlalchemy.engine.Engine [cached since 1825s ago] [{'name': 'cloud', 'age': 18}, {'name': 'alex', 'age': 19}]
2023-12-14 10:56:16,034 INFO sqlalchemy.engine.Engine COMMIT

隐式开启事务

使用engine.begin() 方法，可以隐式开启一个事务。并且执行正常后，会自动提交。如果有异常发送，则会回滚

with engine.begin() as conn:
    conn.execute(text("create table a(x int)"))

2023-12-14 11:52:40,494 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 11:52:40,495 INFO sqlalchemy.engine.Engine create table a(x int)
2023-12-14 11:52:40,495 INFO sqlalchemy.engine.Engine [cached since 2032s ago] {}
2023-12-14 11:52:40,523 INFO sqlalchemy.engine.Engine COMMIT

with engine.begin()  as conn:
    conn.execute(text("create table b(x int)"))
    conn.execute(text("insert into b(x) values(1)"))
    result = conn.execute(text("select * from b"))
    print(result.all())
    conn.execute(text("create table c(x intlong)"))

2023-12-14 11:55:44,902 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 11:55:44,904 INFO sqlalchemy.engine.Engine create table b(x int)
2023-12-14 11:55:44,905 INFO sqlalchemy.engine.Engine [cached since 1925s ago] {}
2023-12-14 11:55:44,932 INFO sqlalchemy.engine.Engine insert into b(x) values(1)
2023-12-14 11:55:44,932 INFO sqlalchemy.engine.Engine [generated in 0.00055s] {}
2023-12-14 11:55:44,934 INFO sqlalchemy.engine.Engine select * from b
2023-12-14 11:55:44,935 INFO sqlalchemy.engine.Engine [generated in 0.00116s] {}
[(1,)]
2023-12-14 11:55:44,936 INFO sqlalchemy.engine.Engine create table c(x intlong)
2023-12-14 11:55:44,937 INFO sqlalchemy.engine.Engine [cached since 177.2s ago] {}
2023-12-14 11:55:44,938 INFO sqlalchemy.engine.Engine ROLLBACK



---------------------------------------------------------------------------

ProgrammingError                          Traceback (most recent call last)

File ~/pythonProjects/SQLAlchemy-v2.0-tutorial/venv/lib/python3.11/site-
ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'intlong)' at line 1")
[SQL: create table c(x intlong)]
(Background on this error at: https://sqlalche.me/e/20/f405)

获取行

with engine.connect() as conn:
    result = conn.execute(text("select * from user"))
    print("查询结果是：")
    for row in result:
        print(row)

2023-12-14 10:56:19,065 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 10:56:19,066 INFO sqlalchemy.engine.Engine select * from user
2023-12-14 10:56:19,067 INFO sqlalchemy.engine.Engine [cached since 1765s ago] {}
查询结果是：
(1, 'cloud', 18)
(2, 'alex', 19)
2023-12-14 10:56:19,069 INFO sqlalchemy.engine.Engine ROLLBACK

绑定参数

with engine.connect() as conn:
    result = conn.execute(text("select * from user where name=:name"),{"name":"cloud"})
    print(result.all())

2023-12-14 10:56:23,113 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 10:56:23,114 INFO sqlalchemy.engine.Engine select * from user where name=%(name)s
2023-12-14 10:56:23,116 INFO sqlalchemy.engine.Engine [cached since 35.77s ago] {'name': 'cloud'}
[(1, 'cloud', 18)]
2023-12-14 10:56:23,118 INFO sqlalchemy.engine.Engine ROLLBACK

绑定多个参数

with engine.connect() as conn:
    result = conn.execute(text("select * from user where name=:name and age=:age"),{"name":"cloud","age":18})
    for row in result:
       print(row)

2023-12-14 11:11:57,727 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 11:11:57,728 INFO sqlalchemy.engine.Engine select * from user where name=%(name)s and age=%(age)s
2023-12-14 11:11:57,729 INFO sqlalchemy.engine.Engine [cached since 877.5s ago] {'name': 'cloud', 'age': 18}
(1, 'cloud', 18)
2023-12-14 11:11:57,731 INFO sqlalchemy.engine.Engine ROLLBACK

元组分配

with engine.connect() as conn:
    result = conn.execute(text("select id,name,age from user"))
    for id,name,age in result:
        print(f"id:{id},name:{name},age:{age}")

2023-12-14 11:13:30,871 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 11:13:30,872 INFO sqlalchemy.engine.Engine select id,name,age from user
2023-12-14 11:13:30,872 INFO sqlalchemy.engine.Engine [generated in 0.00153s] {}
id:1,name:cloud,age:18
id:2,name:alex,age:19
2023-12-14 11:13:30,874 INFO sqlalchemy.engine.Engine ROLLBACK

## 通过索引访问

with engine.connect() as conn:
    result = conn.execute(text("select id,name,age from user"))
    for row in result:
        print(row[0],row[1],row[2])

2023-12-14 11:16:50,814 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-12-14 11:16:50,815 INFO sqlalchemy.engine.Engine select id,name,age from user
2023-12-14 11:16:50,816 INFO sqlalchemy.engine.Engine [cached since 199.9s ago] {}
1 cloud 18
2 alex 19
2023-12-14 11:16:50,818 INFO sqlalchemy.engine.Engine ROLLBACK