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