结合sqlalchemy操作mysql

偏执的太偏执、 2022-05-25 09:25 245阅读 0赞

1.sqlalchemy简介

SQLAlchemy是Python编程语言下的一款开源软件。提供了SQL工具包及对象关系映射(ORM)工具，使用MIT许可证发行。

SQLAlchemy“采用简单的Python语言，为高效和高性能的数据库访问设计，实现了完整的企业级持久模型”。SQLAlchemy的理念是，SQL数据库的量级和性能重要于对象集合;而对象集合的抽象又重要于表和行。因此，SQLAlchmey采用了类似于Java里Hibernate的数据映射模型，而不是其他ORM框架采用的ActiveRecord模型。

优点:

企业级 API，使得代码有健壮性和适应性灵活的设计，使得能轻松写复杂查询

缺点:

工作单元概念不常见  
重量级 API，导致长学习曲线

pip install pymysql  
pip install SQLAlchemy

2.定义数据库配置

define("db\_host",default="127.0.0.1",type=str) \#指定数据库IPdefine("db\_user",default="root",type=str) \#指定数据库用户名define("db\_pwd",default="root",type=str) \#指定数据库密码define("db\_name",default="testdb",type=str) \#指定数据库名称define("db\_port",default=3306,type=int) \#指定数据库端口

3.创建数据库连接池

from sqlalchemy import create\_engineimport pymysql  
engine = create\_engine(

'mysql+pymysql://%s:%s@%s:%d/%s' % (options.db\_user,

options.db\_pwd,options.db\_host,options.db\_port,options.db\_name

),encoding='utf-8',

encoding='utf-8',  
echo=False,  
pool\_size=100,pool\_recycle=10,connect\_args=\{'charset': 'utf8'\}

)

encoding:指定编码  
echo:参数为 True 时，会显示每条执行的 SQL 语句，生产环境下可关闭pool\_size:连接数大小，默认为5，正式环境该数值太小，需根据实际情况调大pool\_recycle:默认为-1，推荐设置为7200，即如果connection空闲了7200秒，自动重新获取，以防止connection被db server关闭max\_overflow:超出pool\_size后可允许的最大连接数，默认为10，这10个连接在使用过后，不放在pool中，而是被真正关闭的pool\_timeout:获取连接的超时阈值，默认为30秒connect\_args:连接参数，charset:字符集

4.将数据库连接加入到tornado中

from sqlalchemy.orm import scoped\_session,sessionmakerself.db = scoped\_session(

sessionmaker(bind=engine,

autocommit=False,autoflush=True,expire\_on\_commit=False

))

sessionmaker():生成一个数据库会话类。这个类的实例可以当成一个数据库连接，它同时还记录了一些查询的数据，并决定什么时候执行 SQL 语句。bind:绑定数据库连接池  
autocommit:True开启自动提交，False关闭autoflush:True开启自动清除缓存，False关闭expire\_on\_commit:True开启，False关闭scoped\_session():它能保证每个线程获得的session对象都是唯一的。5.调用数据库连接

@propertydef db(self):

return self.application.db

6.数据库的增删改查  
创建表:  
sql = u"""  
create table if not exists students(

id int unsigned not null auto\_increment key,trueName varchar(30),  
age tinyint unsigned,  
sex enum("男","女","保密"),

height int,  
weight int,  
info text,  
addtime timestamp

)engine=InnoDB default charset=utf8;"""

self.db.execute(sql)self.db.commit()self.db.close()

增加记录:

sql = u"""  
insert into students(trueName,age,sex,height,weight,info) values("张三",18,1,160,50,"学生信息"),  
("李四",19,2,165,55,"学生信息"),  
("王五",20,3,170,60,"学生信息"),  
("赵六",21,1,175,65,"学生信息"),  
("田七",22,2,180,70,"学生信息");

"""  
lastrowid = self.db.execute(sql).lastrowidself.db.commit()  
self.db.close()self.write("<h1>最后的记录ID:%d</h1>" % lastrowid)

查询记录:

sql = "select \* from students"\# 取出第一条记录

data1 = self.db.execute(sql)returnsrows = data1.returns\_rowsdata1\_list = data1.first()  
\# 取出一条记录

data2 = self.db.execute(sql).fetchone()

data2 = self.db.execute(sql).fetchone()\# 取出所有记录  
data3 = self.db.execute(sql).fetchall()\# 取出前n条记录

data4 = self.db.execute(sql).fetchmany(2)  
\# 获取一个不存在的记录  
data5 = self.db.execute("select \* from students where id = 99")data5\_returnsrows = data5.returns\_rows  
self.write(str(data1\_list))  
self.write("<hr><br>")  
self.write("返回记录的状态:%s" % str(returnsrows))self.write("<hr><br>")  
self.write(str(data2))  
self.write("<hr><br>")  
self.write(str(data3))  
self.write("<hr><br>")  
self.write(str(data4))  
self.write("<hr><br>")  
self.write("data5查询返回记录的状态:%s" % str(data5\_returnsrows))

修改记录:

sql = u"update students set info = '学生的个人信息' where id in (1,3,5)"rowcount = self.db.execute(sql).rowcount  
self.db.commit()  
self.db.close()

self.write("<h1>修改了%d条记录!</h1>" % rowcount)

删除记录:

sql = "delete from students where id in (2,4)"  
rowcount = self.db.execute(sql).rowcountself.db.commit()  
self.db.close()self.write("<h1>删除了%d条记录!</h1>" % rowcount)