如何使用MySQL,这些操作你得明白?

来自:googpy(微信号:googpy),作者:stormwen

阅读文本大概需要 6 分钟。

昨天给大家介绍了安装MySQL数据库的方法,大家都学会了吗?

 

MySQL数据库是基于关系模型的数据库,而关系模型是通过二维表来保存的,所以它的储存方式就是行列组成的表,每一列是一个字段,每一行是一条记录。今天我们主要介绍Python3下使用PyMySQL操作MySQL数据库的方法。


1.准备工作


在开始之前,我们需要安装好PyMySQL库,安装方法仍然是我们老生常谈的方法,用pip安装,命令如下:

pip3 install pymysql

执行完命令后即可完成安装。

 

2.连接数据库


假设当前的MySQL运行在本地,用户名为root,密码为123456,运行端口为3306。然后用PyMySQL先连接MySQL,创建了一个新的数据库,命名为spiders,代码如下:

 

import pymysql

db = pymysql.connect(host='localhost',user='root',password='123456',port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
data = cursor.fetchone()
print('Database version:',data)
cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8")
db.close()


运行结果如下:

 

Database version: ('5.7.23',)


连接成功后,需要调用cursor()方法获得MySQL的操作游标,利用游标来执行SQL语句。这里我们执行了两句SQL,直接用execute()方法即可。第一句SQL用于获得MySQL的当前版本,然后调用fetchone()方法获得第一条数据,也就得到了版本号。

 

第二句SQL执行创建数据库的操作,数据库命名为spiders,默认编码为UTF-8。

 

3.创建表


一般来说,创建数据库的操作只需执行一次就好。当然,我们也可以手动创建数据库。以后我们的操作都在spiders数据库上执行。创建数据库后,在连接时需要额外指定一个参数db。

 

例如,我们创建一个数据包students,此时执行创建表的SQL语句即可。这里指定3个字段(id、name、age),创建该表的示例代码如下:

 

import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')
cursor = db.cursor()
sql = 'CREATE TABLE IF NOT EXISTS students (id VARCHAR(255) NOT NULL,name VARCHAR(255) NOT NULL,age INT NOT NULL,PRIMARY KEY (id))'
cursor.execute(sql)
db.close()

运行之后,我们便创建了一个名为students的数据表。

 

4.插入数据


例如,这里爬取了一个学生信息,学号为20120001,名字为Bob,年龄20,那么如何将该条数据插入数据库呢?示例代码如下:

 

import pymysql
id = '20120001'
user = 'Bob'
age = 20
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')
cursor = db.cursor()
sql = 'INSERT INTO student(id,name,age) values(%s,%s,%s)'
try:
   cursor.execute(sql,(id,user,age))
   db.commit()
except:
   db.rollback()
db.close()


这里首先构造了一个SQL语句,其Value值没有用字符串拼接的方式来构造,选择直接用格式化符%s来实现,有几个Value写几个%s,我们只需在execute()方法的第一个参数传入SQL语句,Value值用统一的元组传过来就好了。

 

之后值得注意的是,需要执行db对象的commit()方法才可实现数据插入,这个方法才是真正将语句提交到数据库执行的方法对于数据插入、更新、删除操作,都需要调用该方法才能生效。

 

接下来,我们加一层异常处理,如果执行失败,则调用rollback()执行数据回滚,相当于什么都没发生过。

 

上面数据插入的操作是通过构造SQL语句实现的,但有一个极其不方便的地方,比如突然增加了性别字段gender,此时SQL语句就需要改成:

 

INSERT INTO students(id,name,age,gender) values(%s,%s,%s,%s)


相应的元组参数则需要改成:

 

(id,name,age,gender)


这显然不是我们需要的。在很多情况下,我们要达到的效果就是插入方法无需改动,做成一个通用方法,只需要传入一个动态变化的字典就好了。

 

然后SQL语句会根据字典动态构造,元组也动态构造,这样才能实现通用的插入方法。所以,这里我们需要改写一下插入方法:

 

import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')
cursor = db.cursor()

data ={
   'id':'20120001',
   'name':'Bob',
   'age':20
}
table = 'Students'
keys = ', '.join(data.keys())
values = ', '.join(['%s']*len(data))
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table,keys=keys,values=values)
try:
   if cursor.execute(sql,tuple(data.values())):
       print('Successful')
       db.commit()
except:
   print('Failed')
   db.rollback()
db.close()


如此以来,我们便实现了传入一个字典来插入数据的方法,不需要再去修改SQL语句和插入操作了。

 

5.更新数据


数据更新操作实际上也是执行SQL语句,最简单的方式就是构造一个SQL语句,然后执行:

 

sql = 'UPDATE students SET age =%s WHERE name = %s'
try:
   cursor.execute(sql,(25,'
Bob'))
   db.commit()
except:
   db.rollback()
db.close()


在实际的数据抓取过程中,大部分情况下需要插入数据,但我们关心的是会不会出现重复数据,如果出现了,我们希望更新数据而不是重复保存一次。因此,这里可以再实现一种去重的方法,如果数据存在,则更新数据;如果数据不存在,则插入数据。示例如下:

 

import pymysql
db = pymysql.connect(host='localhost',user='root',password='123456',port=3306,db='spiders')
cursor = db.cursor()

data ={
   'id':'20120001',
   'name':'Bob',
   'age':20
}
table = 'Students'
keys = ', '.join(data.keys())
values = ', '.join(['%s']*len(data))
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table,keys=keys,values=values)
update = ', '.join(["{key} = %s".format(key=key) for key in data])
sql += update
try:
   if cursor.execute(sql,tuple(data.values())*2):
       print('Successful')
       db.commit()
except:
   print('Failed')
   db.rollback()
db.close()


这里构造的SQL语句其实是插入语句,但我们在后面加了ON DUPLICATE KEYUPDATE。这行代码的意思是如果主键已经存在,就执行更新操作。

 

比如,我们传入的数据id仍然为20120001,但是年龄有所变化,由20变成了21,此时这条数据不会被插入,而是直接更新id为20120001的数据。

 

6.删除数据


删除操作先对简单,直接使用DELETE语句即可,只是需要指定要删除的目标表名和删除条件,而且仍然需要使用db的commit()方法才能生效。示例如下:

 

table = 'students'
condition = 'age > 20'
sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table,condition=condition)
try:
   cursor.execute(sql)
   db.commit()
except:
   db.rollback()
   
db.close()


7.查询数据


查询会用到SELECT语句,示例如下:

 

sql = 'SELECT * FROM students WHERE age >=20'

try:
   cursor.execute(sql)
   print('Count:',cursor.rowcount)
   one = cursor.fetchone()
   print('One:',one)
   results = cursor.fetchall()
   print('Results:',results)
   print('Results Type:',type(results))
   for row in results:
       print(row)
except:
   print('Error')


运行结果如下:

 

Count:4
One:('20120001','Bob',25)
Results:(('20120011','Mary',21),('20120012','Mike',20),('20120013','James',22)
Results Type:<class 'tuple'>
('20120011','Mary',21)
('20120012','Mike',20)
('20120013','James',22)


这里我们构造了一条SQL语句,将年龄20岁及以上的学生查询出来,然后将其给excute()方法。注意,这里不需要db的commit()方法。接着,调用cursor的rowcount属性获取查询结果的条数,当前示例中是4条。

 

但是这里需要注意一个问题,这里显示的是3条数据而不是4条,fetchall()方法不是获取所有数据吗?这个问题留给大家思考,下一篇公布答案。

 

总结


今天我们介绍了如何使用PyMySQL操作MySQL数据库的方法,主要包括:插入、更新、删除、查询数据,后面我会给大家找一些经典的实战案例,让大家在实战中熟悉运用这些操作来储存数据。

推荐↓↓↓
数据库开发
上一篇:如何选型一个合适的框架-分布式任务调度框架选型 下一篇:5分钟安装好MySQL数据库(建议收藏)