flask sql alchemy 存储过程 原生sql sqlalchemy执行原生sql

文章目录

• 操作步骤

• 第一步：获得连接对象
• 第二步：利用text函数创建原生sql
• 第三步：利用连接对象执行原生sql
• 第四步：关闭连接对象
• 步骤整合

• 数据库操作示例

• 创建表格
• 增加记录
• 查询记录

• 无条件查询
• 有条件查询

• 更改记录
• 删除记录
• 完整代码

操作步骤

第一步：获得连接对象

利用原生SQL与DBAPI进行事务处理时，需首先获得一个数据库引擎的连接对象。

from sqlalchemy import create_engine
dburl = 'mysql+pymysql://root:123456@127.0.0.1:3306/testdb'
# 获取数据库引擎
engine = create_engine(url=dburl)
# 获取数据库连接对象
conn = engine.connect()

第二步：利用text函数创建原生sql

利用sqlalchemy中的text函数将sql字符串加工为原生sql。

from sqlalchemy import text
sql_text = "SELECT 'hello sqlalchemy'"
sql = text(sql_text)

第三步：利用连接对象执行原生sql

连接对象执行原生sql后，才能真正与数据进行事务交互。

result = conn.execute(sql)
print(result.all())

第四步：关闭连接对象

由于连接对象也是一种资源，所以在事务处理完后要及时关闭资源。

conn.close()

步骤整合

可以利用Python的上下文管理器的方式使用连接对象，用完后连接对象自动关闭。

with engine.connect() as conn:
	result = conn.execute(text(sql_text))
	print(result.all())

数据库操作示例

创建表格

创建表格时，text函数中的sql语句和MySQL中创建表格的语句一致。

with engine.connect() as conn:
	result = conn.execute(text("CREATE TABLE student(id int,name varchar(20), age smallint)"))

增加记录

增加记录时，text函数中sql语句和MySQL中略有不同，主要表现在变量的占位表示上。
在VALUES的括号中利用:id表示一个占位符且名称为id，:name，:age用相同的理解方式。
然后利用execute函数的下一个参数来填充这些占位符。该参数是一个列表或字典，列表中存放着字典，字典的键就是这些占位符的名称。

with engine.connect() as conn:
	result = conn.execute(text("INSERT INTO student(id, name, age) VALUES(:id, :name, :age)"),
		[{"id":1, "name":"jack", "age":28},{"id":2, "name":"tom", "age":18},{"id":3, "name":"rose", "age":19}])

查询记录

无条件查询

无条件查询时，text函数中的sql语句和MySQL中一致。
获取的结果是一个可迭代对象，每次迭代时可以通过下标，属性名或键值对的方式获取相应的列的值。

with engine.connect() as conn:
	students = conn.execute(text("SELECT name,age FROM student"))
	# 这是两种方式
	for stu in students:
		print(stu[0],stu[1])  #下标的方式，stu[0]表示name，stu[1]表示age
		print(stu.name,stu.age) #属性的方式
	# 这是另外一种方式
	for name, age in students:
		print(name,age)

有条件查询

有条件查询时，text函数中的sql语句中通常包含参数，需要用到text函数中占位符的形式书写sql。

with engine.connect() as conn:
	students = conn.execute(text("SELECT name, age FROM student where age < :age"),{"age":20})
	for name, age in students:
		print(name,age)

更改记录

更改记录也是用到了占位符的形式来书写sql。

with engine.connect() as conn:
	conn.execute(text("UPDATE TABLE student SET name=:name WHERE id=:id"),{"name":"Rose","id":3})

删除记录

删除记录也是用到了占位符的形式来书写sql

with engine.connect() as conn:
	conn.execute(text("DELETE FROM student WHERE id=:id"),{"id":2})

完整代码

from sqlalchemy import create_engine,text

dburl = 'mysql+pymysql://root:123456@127.0.0.1:3306/testdb'
# 获取数据库引擎
engine = create_engine(url=dburl)

# 增加数据
with engine.connect() as conn:
	result = conn.execute(text("INSERT INTO student(id, name, age) VALUES(:id, :name, :age)"),
		[{"id":1, "name":"jack", "age":28},{"id":2, "name":"tom", "age":18},{"id":3, "name":"rose", "age":19}])

# 无条件查询
with engine.connect() as conn:
	students = conn.execute(text("SELECT name,age FROM student"))
	# 这是两种方式
	for stu in students:
		print(stu[0],stu[1])  #下标的方式，stu[0]表示name，stu[1]表示age
		print(stu.name,stu.age) #属性的方式
	# 这是另外一种方式，由于上面的迭代已经结束，下面的不会执行
	for name, age in students:
		print(name,age)
print("*" * 20)
# 有条件查询
with engine.connect() as conn:
	students = conn.execute(text("SELECT name, age FROM student where age < :age"),{"age":20})
	for name, age in students:
		print(name,age)
print("*" * 20)
#  更改记录
with engine.connect() as conn:
	conn.execute(text("UPDATE student SET name=:name WHERE id=:id"),{"name":"Rose","id":3})

# 删除记录
with engine.connect() as conn:
	conn.execute(text("DELETE FROM student WHERE id=:id"),{"id":2})

执行结果：

数据库中最后的表内容：