随着需要存储数据的结构不断复杂化，使用数据库来存储数据是一个必须面临的问题。那么应该如何在python中使用数据库？下面就在本篇博客中介绍一下在python中使用mysql。
首先，本博客已经假定阅读者已经安装了python和mysql，所以不会讲解关于它们的安装(如果未安装，请查阅官方文档进行下载安装)。
在python中使用pymysql操作mysql
python的标准库中，是没有可以直接连接操作mysql的模块，首先我们应安装python的第三方模块pymysql。
使用pymysql操作mysql的步骤：
1)使用pymysql.connect连接并登录mysql
2) 使用connection.cursor建立游标
3) 使用cursor.execute()或cursor.executemany()执行sql语句
例一(使用pymysql执行简单的mysql操作)：
(1) 首先在mysql中建立一张用户表
CREATE TABLE`users` (
`id`int(11) NOT NULLAUTO_INCREMENT,
`email`varchar(255) COLLATE utf8_bin NOT NULL,
`password`varchar(255) COLLATE utf8_bin NOT NULL,PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;
(2) 使用pymysql连接数据库并操作这张表
1 importpymysql2
3 #Connect to the database
4 #连接mysql，host指定主机；port指定端口，如果mysql为默认端口3306可以不写；
5 #user,password分别指定登录mysql的用户名和密码；
6 #db指定数据库；charset指定字符集；
7 connection = pymysql.connect(host='localhost',8 user='root',9 password='',10 db='test',11 charset='utf8mb4',12 cursorclass=pymysql.cursors.DictCursor)13
14 try:15 with connection.cursor() as cursor:16 #Create a new record
17 #构建sql语句
18 sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
19 #相当于在mysql终端执行
20 #"INSERT INTO `users` (`email`, `password`) VALUES ('webmaster@python.org', 'very-secret')"
21 cursor.execute(sql, ('webmaster@python.org', 'very-secret'))22
23 #connection is not autocommit by default. So you must commit to save
24 #your changes.
25 #向mysql提交更改，如果是查询语句，无需执行connection.commit()
26 #可以通过设置connection.autocommit()来自动提交，传入True即可
27 connection.commit()28
29 with connection.cursor() as cursor:30 #Read a single record
31 #sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
32 #cursor.execute(sql, ('webmaster@python.org',))
33 sql = "SELECT * FROM `users`"
34 #执行cursor.execute(sql),等于在mysql终端执行sql语句。
35 cursor.execute(sql)36 #获取sql语句执行结果并打印
37 result =cursor.fetchall()38 print(result)39 finally:40 #关闭连接
41 connection.close()
pymysql_example.py
例二(向mysql中的表插入多条信息)：
1 importpymysql2
3 connection = pymysql.Connect(host="localhost",4 user="root",5 password="",6 db="test",7 charset="utf8mb4",8 cursorclass=pymysql.cursors.DictCursor)9
10 try:11 ## 执行多次INSERT操作
12 #with connection.cursor() as cursor:
13 #users_info = [('xiaoming@123.com','simple'), ('xiaoqiang@123.com','simple'),
14 #('xiaozhang@123.com','very-secret'), ('xiaoli@123.com', 'simple'),
15 #('xiangwang@123.com','simple'), ('xiaohong@123.com','very-secret')]
16 #sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
17 ## 执行多次相同操作使用cursor.executemany()
18 #cursor.executemany(sql, users_info)
19 #connection.commit()
20
21 #查询所有用户信息
22 with connection.cursor() as cursor:23 sql = "SELECT * FROM `users`"
24 cursor.execute(sql)25 result =cursor.fetchall()26 print("-----all users-----")27 for user_info inresult:28 print(user_info)29
30 with connection.cursor() as cursor:31 sql = "SELECT * FROM `users` WHERE `password`=%s"
32 cursor.execute(sql, ('very-secret',))33 result =cursor.fetchall()34 print("-----password is very-secret-----")35 for user_info inresult:36 print(user_info)37 finally:38 connection.close()
test_pymysql.py
注：在python程序中使用pymysql，最好只执行对表的增删该查即可(使用pymysql虽然能执行原生SQL语句，但不建议使用它进行建数据库，表，修改数据库，表属性等操作(如果要进行这些操作不妨直接登录mysql，直接在mysql终端执行这些操作)。
下面将介绍一些pymysql的一些常用API(在pymysq中只有两个常用object)：
(1)Connection Object：
常用属性：
host – mysql主机地址
user – 登录用户名
password – 登录用户密码
port – mysql端口，默认3306
charset – 字符集
connect_timeout – 连接最大时间，超时自动断开。(default:10, min: 1, max: 31536000)
autocommit – 是否自动提交更改。(default: False)
db – 使用指定的数据库
cursorclass – 指定cursor类
注：以上参数应在连接数据库时指定，只是常用参数(详细请参见：http://pymysql.readthedocs.io/en/latest/modules/connections.html)。
常用方法：
begin() -开启一个事件 与 在mysql终端执行BEGIN效果相同
close()-关闭与mysql的连接
commit()-提交对mysql中存储数据的更改
cursor(cursor=None) -创建一个cursor对象，cursor类在连接时未指明，可以在此指明，使用默认cursor忽略参数即可
ping(reconnect=True) -检测连接是否存活，如果连接超过设置的connet_timeout会自动断开，所以在进行对mysql操作前应使用此方法检测
rollback()-使用了begin()后，对mysql的操作未提交前，可以只用此方法恢复到未操作之前
select_db(db)-选择数据库，如果要操作的表不在连接时指定的数据库，使用此方法切换。
show_warnings()- 显示警告信息
(2)Cursor Objects：
常用方法：
execute(query, args=None) -执行一条sql语句
Parameters:
query (str) – 要被执行的sql语句
args (tuple, listordict) – sql语句中用到的参数
Returns:
多少行信息收到影响
Return type:
int
如果args是以tuple的形式指定，则按位置依次传入sql语句中；如果是以dict传入，则以关键字传入sql语句中。
executemany(query, args)-多次执行这条sql语句
参数与上相同，不过要使用[]将多个args括起来。
此方法可提高多行INSERT和REPLACE的性能。 否则，它等价于使用execute() 循环args。
fetchone()-取结果中的一行
fetchall()-取所有的结果
fetchmany(size=None) -取结果中的size行
close()-关闭当前cursor
max_stmt_length= 1024000 - 指定 executemany() 执行最多max_stmt_length次sql语句
注：只写了常用方法，详细请参见：http://pymysql.readthedocs.io/en/latest/modules/cursors.html
使用sqlalchemy操作数据库(重点)
例三(使用sqlalchemy创建一张数据表并插入数据)：
使用pymysql固然可以与mysql进行交互，但还是在源代码中使用了原生SQL语句，使代码的重用行和扩展性大大降低，这不符合面向对象的编程的特性。那么该如何像操作对象一样操作数据库呢？
我们使用一种叫做ORM(Object Relational Mapping，简称ORM，或O/RM，或O/R mapping)的技术，是一种程序技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。在python中我们使用一个名为SQLAlchemy(基于ORM的开发组件)来进行对数据库的操作，这样就不必在源代码中使用SQL语句，大大降低了程序员学习SQL的成本，由于不必再拼接复杂的SQL语句，大大提高开发效率，并且使程序有更高的扩展性。
1 importsqlalchemy2 from sqlalchemy importcreate_engine3 from sqlalchemy.ext.declarative importdeclarative_base4 from sqlalchemy importColumn, Integer, String5 from sqlalchemy.orm importsessionmaker6
7 #检查sqlalchemy的版本
8 #print(sqlalchemy.__version__)
9
10 #创建一个engine
11 #传入一个URL作为第一个位置参数(格式为：dialect[+driver]://user:password@host/dbname[?key=value..])
12 #dialect is a database name such as mysql, oracle, postgresql, ,
13 #and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle, pymysql.
14 #打印操作数据库的过程，则设置echo=True，否则默认即可
15 engine = create_engine('mysql+pymysql://root:123456@localhost/test')16
17 Base =declarative_base()18
19 #将要创建的表结构
20 classUser(Base):21 #表名
22 __tablename__ = 'users'
23
24 #字段名，字段属性
25 id = Column(Integer, primary_key=True)26 name = Column(String(32))27 fullname = Column(String(64))28 password = Column(String(64))29
30 def __repr__(self):31 return "" %(32 self.name, self.fullname, self.password)33
34 #可以同时创建多个表，在前面以上面的形式写好所有表结构，最后统一创建
35 Base.metadata.create_all(engine)36
37 #创建一个Session类
38 #Session = sessionmaker()
39 #Session.configure(bind=engine)
40 #等同于上面两行
41 Session = sessionmaker(bind=engine)42 #生成一个session实例
43 session =Session()44
45 #构造要插入表中的数据
46 ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')47 #将数据放入session中，如果有多条数据使用session.add_all([data1,data2,...])
48 session.add(ed_user)49 #session.add_all([User(name='wendy', fullname='Wendy Williams', password='foobar'),
50 #User(name='mary', fullname='Mary Contrary', password='xxg527'),
51 #User(name='fred', fullname='Fred Flinstone', password='blah')])
52 #向数据库提交
53 #session.commit()
54
55 data = session.query(User).filter(User.id>2).all()56 print(data)
sqlalchemy_test.py
# 使用上面的代码生成的数据表结构
mysql> descusers;+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
| fullname | varchar(64) | YES | | NULL | |
| password | varchar(64) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00sec)
# 使用上面代码插入表中的数据
mysql> select * fromusers;+----+------+----------+-------------+
| id | name | fullname | password |
+----+------+----------+-------------+
| 1 | ed | Ed Jones | edspassword |
+----+------+----------+-------------+
1 row in set (0.00 sec)
例四(使用sqlalchemy进行对数据的查，改，删)
1 #查询时在filter_by(或filter)中写上条件即可，查询到的结果可能是多条，first()代表取第一条，all()代表取所有
2 our_user = session.query(User).filter_by(name='ed').first()3 #如果有多个查询条件，data = session.query(User).filter(User.id>2).filter(User.id<4).all(),这样使用即可
4 data = session.query(User).filter(User.id>2).all()5 print("-------这是查询数据的结果-------")6 print(our_user)7 print(data)8 print('\n')9
10 #直接修改查询的结果，然后提交即可
11 our_user.password = 'f8s7ccs'
12 session.commit()13 new_user = session.query(User).filter_by(name='ed').first()14 print("-------这是修改数据的结果-------")15 print(new_user)16 print('\n')17
18 #先查询出要删除的数据，然后使用session.delete()和session.delete()即可
19 data = session.query(User).filter(User.id==5).first()20 #print(data)
21 session.delete(data)22 session.commit()
使用sqlalchemy操作数据库中的数据
例五(使用sqlalchemy实现数据表的外键关联)：
作为关系型数据库，表与表之间的外键关联是比不可少的，也是至关重要的，那么改如何使用sqlalchemy在python对象中通过类的形式映射这种关系呢？ 请看下面的代码。
1 importsqlalchemy2 from sqlalchemy importcreate_engine3 from sqlalchemy.ext.declarative importdeclarative_base4 from sqlalchemy importColumn, Integer, String, Enum, ForeignKey5 from sqlalchemy.orm importsessionmaker, relationship6
7 engine = create_engine('mysql+pymysql://root:123456@localhost/student')8
9 Base =declarative_base()10
11 classStudent(Base):12 __tablename__ = 'student_info'
13
14 #设置id, 类型为int, 不能为空, id是这张表的主键
15 id = Column(Integer, nullable=False, primary_key=True)16 #设置stu_id, 类型为int, 不能为空, id在这张表中的值唯一
17 stu_id = Column(Integer, nullable=False, unique=True)18 name = Column(String(32), nullable=False, )19 age = Column(Integer, nullable=False, )20 gender = Column(Enum('F', 'M'), nullable=False)21
22 #查询结果的显示是此函数返回的格式
23 def __repr__(self):24 return "" %(25 self.stu_id, self.name, self.age, self.gender)26
27 classStudy(Base):28 __tablename__ = 'study_level'
29
30 id = Column(Integer, nullable=False, primary_key=True)31 #设置stu_id为study_level表的外键，与student_info表中的stu_id关联
32 stu_id = Column(Integer, ForeignKey('student_info.stu_id'))33 mathematics =Column(Integer)34 physics =Column(Integer)35 chemistry =Column(Integer)36
37 #定义关系，可以在本类中使用属性student_info查询表student_info中的数据(以同样的条件)
38 #也可以在Student类中使用属性study_level查询表study_level中的数据
39 student_info = relationship('Student', backref='study_level')40
41 def __repr__(self):42 return "" %(43 self.student_info.name, self.mathematics, self.physics, self.chemistry)44
45 #Base.metadata.create_all(engine)
46
47 Session =sessionmaker(engine)48 session =Session()49
50 #插入4个学生信息
51 #session.add_all([Student(stu_id=10001, name='zhangsan', age=16, gender='F'),
52 #Student(stu_id=10002, name='lisi', age=17, gender='M'),
53 #Student(stu_id=10003, name='wangwu', age=16, gender='M'),
54 #Student(stu_id=10004, name='zhouliu', age=15, gender='F')])
55 #56 #插入考试成绩，成绩不到60分的科目需补考，再插入补考成绩
57 #session.add_all([Study(stu_id=10001, mathematics=78, physics=70, chemistry=83),
58 #Study(stu_id=10002, mathematics=87, physics=85, chemistry=92),
59 #Study(stu_id=10003, mathematics=60, physics=54, chemistry=76),
60 #Study(stu_id=10004, mathematics=52, physics=46, chemistry=44),
61 #Study(stu_id=10003, physics=68),
62 #Study(stu_id=10004, mathematics=63, physics=61, chemistry=65)])
63 #session.commit()
64
65 #使用这种方法查询多张表，表之间可以没有任何关系
66 data = session.query(Student, Study).filter(Student.stu_id==Study.stu_id).all()67 print(data)68 print('\n')69
70
71 #使用下面的方法通过一张表查询其他表，表之间必须有外键关联
72 #因为每个学生的信息唯一，所以使用first()
73 student = session.query(Student).filter(Student.stu_id==10003).first()74 print(student)75 #print(student.study_level)相当于Student.stu_id==10003时，下面的两行代码
76 #data = session.query(Study).filter(session.query(Study).filter(Student.stu_id==Study.stu_id).all()).all()
77 #print(data)
78 print(student.study_level)79 print('\n')80
81 #因为一个学生可能会有多次考试记录，所以使用all()
82 score = session.query(Study).filter(Study.stu_id==10003).all()83 print(score)84 #print(score[0].student_info)相当于Study.stu_id==10003时
85 #因为在student_info表中stu_id的值唯一，所以只有一条数据
86 #data = session.query(Student).filter(Study[0].stu_id==Student.stu_id).first()
87 #print(data)
88 print(score[0].student_info)
fk_sqlalchemy.py
mysql> select * fromstudent_info;+----+--------+----------+-----+--------+
| id | stu_id | name | age | gender |
+----+--------+----------+-----+--------+
| 1 | 10001 | zhangsan | 16 | F |
| 2 | 10002 | lisi | 17 | M |
| 3 | 10003 | wangwu | 16 | M |
| 4 | 10004 | zhouliu | 15 | F |
+----+--------+----------+-----+--------+
4 rows in set (0.00sec)
mysql> select * fromstudy_level;+----+--------+-------------+---------+-----------+
| id | stu_id | mathematics | physics | chemistry |
+----+--------+-------------+---------+-----------+
| 1 | 10001 | 78 | 70 | 83 |
| 2 | 10002 | 87 | 85 | 92 |
| 3 | 10003 | 60 | 54 | 76 |
| 4 | 10004 | 52 | 46 | 44 |
| 5 | 10003 | NULL | 68 | NULL | #学号为10003的学生，只有一科成绩小于60，只补考一科
| 6 | 10004 | 63 | 61 | 65 | #学号为10004的学生，三科成绩都小于60，需补考三科
+----+--------+-------------+---------+-----------+
6 rows in set (0.00 sec)
注：对有外键关联的数据表，进行数据的增删该查，与上例中使用的方式一样，不过受外键约束，约束条件同mysql中外键的约束相同。(详细请参见：http://www.cnblogs.com/God-Li/p/8157312.html)
例六(使用sqlalchemy实现mysql中多对多的关系)：
多对多的数据关系是最常见的实际生产的数据关系，比如超市的商品与顾客之间的关系(一个顾客可以买多种商品，一种商品可以被多个顾客购买)，比如电影与演员的关系(一名演员可以参演多部电影，一部电影会有多个演员)，这些数据是我们经常使用的，比如我们在视频网站查找电影时，会有按演员查找，对于一部电影我们也经常关注是哪些演员参演的。那么改如何使用sqlalchemy在mysql中存储这些关系呢？我们就以超市商品与顾客之间的关系来做一个示例，请看下面的代码。
为了便于理解，我们先来看一下表结构(一共三张表)
# 商品表，存储商品的名称，价格，和生产日期(为了简单只存这几样信息)
mysql> select * fromproducts;+----+-------------+-------+------------+
| id | name | price | pro_date |
+----+-------------+-------+------------+
| 1 | iPhone8 | 6988 | 2017-09-18 |
| 2 | Apple Watch | 2588 | 2017-06-20 |
| 3 | Airpods | 1288 | 2017-01-11 |
| 4 | MacBook | 10288 | 2017-05-13 |
+----+-------------+-------+------------+
4 rows in set (0.00sec)
# 顾客表，存储顾客的姓名(这里为了简单只存了姓名，其实还应该用性别、年龄等具体信息)
mysql> select * fromcustomers;+----+-----------+
| id | name |
+----+-----------+
| 1 | ZhangSang |
| 2 | WangWu |
| 3 | XiaoMing |
| 4 | LiSi |
| 5 | ZhaoLiu |
+----+-----------+
5 rows in set (0.00sec)
# 商品顾客关系表，存储商品与用户的关系，可通过用户查购买了哪些商品，也可通过商品查有哪些用户购买
mysql> select * fromproduct_to_customer;+------------+-------------+
| product_id | customer_id |
+------------+-------------+
| 4 | 4 |
| 4 | 3 |
| 3 | 2 |
| 2 | 1 |
| 2 | 4 |
| 2 | 2 |
| 2 | 5 |
| 2 | 3 |
| 1 | 1 |
| 1 | 4 |
| 1 | 5 |
+------------+-------------+
11 rows in set (0.00 sec)
接着我们来看一下如何使用python来创建这些表，插入并查询这些信息。
1 importsqlalchemy2 from sqlalchemy importTable, Column, Integer, String, DATE, ForeignKey3 from sqlalchemy.orm importrelationship4 from sqlalchemy.ext.declarative importdeclarative_base5 from sqlalchemy importcreate_engine6
7 Base =declarative_base()8
9 #商品与顾客关系表结构
10 product_to_customer = Table('product_to_customer', Base.metadata,11 Column('product_id', Integer, ForeignKey('products.id')),12 Column('customer_id', Integer, ForeignKey('customers.id')))13
14 #用户表结构
15 classCustomer(Base):16 __tablename__ = 'customers'
17
18 id = Column(Integer, primary_key=True)19 name = Column(String(32))20
21 def __repr__(self):22 returnself.name23
24 #商品表结构
25 classProduct(Base):26 __tablename__ = 'products'
27
28 id = Column(Integer, primary_key=True)29 name = Column(String(32))30 price =Column(Integer)31 pro_date =Column(DATE)32 customers = relationship(Customer, backref='products', secondary='product_to_customer')33
34 def __repr__(self):35 returnself.name36
37
38 engine = create_engine('mysql+pymysql://root:123456@localhost/supermarket')39 Base.metadata.create_all(engine)
table_struct.py
1 importtable_struct2 from sqlalchemy.orm importsessionmaker3
4 Session =sessionmaker(table_struct.engine)5 session =Session()6
7 #构建商品信息
8 #p1 = table_struct.Product(name='iPhone8', price='6988', pro_date='2017-9-18')
9 #p2 = table_struct.Product(name='MacBook', price='10288', pro_date='2017-5-13')
10 #p3 = table_struct.Product(name='Airpods', price='1288', pro_date='2017-1-11')
11 #p4 = table_struct.Product(name='Apple Watch', price='2588', pro_date='2017-6-20')
12 #
13 #构建顾客信息
14 #c1 = table_struct.Customer(name="ZhangSang")
15 #c2 = table_struct.Customer(name="LiSi")
16 #c3 = table_struct.Customer(name="WangWu")
17 #c4 = table_struct.Customer(name="ZhaoLiu")
18 #c5 = table_struct.Customer(name="XiaoMing")
19 #20 #构建商品与顾客的关系
21 #p1.customers = [c1, c2, c4]
22 #p2.customers = [c2, c5]
23 #p3.customers = [c3]
24 #p4.customers = [c1, c2, c3, c4, c5]
25 #26 #session.add_all([p1, p2, p3, p4, c1, c2, c3, c4, c5])
27 #session.commit()
28
29 #通过顾客查询他购买了哪些商品
30 customer_obj = session.query(table_struct.Customer).filter(table_struct.Customer.name=='XiaoMing').first()31 print(customer_obj.products)32
33 #通过商品查询有哪些顾客购买
34 product_obj = session.query(table_struct.Product).filter(table_struct.Product.name=="iPhone8").first()35 print(product_obj.customers)
database_api.py
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
