我的python学习笔记——<python对接关系数据库MySQL的存储>

hahally

在做爬虫的抓取数据时候，我们抓取的数据一般需要通过数据库来存储。python与数据库的对接是非常有必要的。

上一篇笔记：

代理的设置和代理池的维护，就需要用到非关系数据库redis来存储，其实选择关系数据库mysql也是可以的。

现在学到python的mysql的存储，发现上学期的数据库概论好像白学了。python需要对接MySQL，然后传递SQL语句给MySQL 执行命令。然而SQL语句只记得个SELECT了。看来还得需要补补SQL的知识了。

#-----------------关系数据库的存储#Mysql的存储--------------------# #关系数据库是基于关系模式的数据库，而关系模型是通过二维表来保存的 # #所以它的存储方式就是行列组成的表，每一列是一个字段，每一行就是一 # #条记录。表可以看作某个实体的集合，而实体之间存在联系，这就需要表 # #与表之间的关联关系来体现，如主键外键的关联关系。多个表组成一个数 # #据库，也就是关系型数据库 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # import pymysql #第一部分#数据库的连接# db = pymysql.connect(host='localhost',user='root') #通过pymysql的connect()方法声明一个mysql连接对象db,传入mysql运行的host(即ip) #因为mysql运行在本地，所以传入的是localhost。如果mysql在远程运行，则传入其公网 #IP地址。可选参数user用户名，password密码，port端口（默认3306）。 cursor = db.cursor() #连接成功后，调用cursor()方法获得mysql的操作游标，利用游标来执行SQL语句。 cursor.execute('SELECT VERSION()') #直接使用execute()方法执行SQL语句。获得MySQL的当前版本。 data = cursor.fetchone() #调用fetchone()方法获得第一条数据，也就是得到版本号。 print('Database:',data) #打印版本号 db.close() #关闭数据库 cursor.execute('CREATE DATABASE spiders DEFAULT CHARACTER SET utf8') #SQL执行创建数据库的操作，创建名为spider的数据库。 #由于该语句不是查询语句，所以直接执行后就成功创建了数据库spider。 #第二部分#创建表# db = pymysql.connect(host='localhost',user='root',db='spiders') #参数db='spiders',选着已创建的数据库。 cursor = db.cursor() #获得操作游标 sql = 'CREATE TABLE IF NOT EXISTS student(id VARCHAR(255) NOT NULL,name VARCHAR(255) NOT NULL,age INT NOT NULL,PRIMARY KEY(id))' #创建表的SQL语句#表名为student# cursor.execute(sql) #执行SQL语句 db.close() #关闭数据库 #第三部分#插入数据# id = '20172011xxxx' name = 'hahally' age = 20 db = pymysql.connect(host='localhost',user='root',db='spiders') cursor = db.cursor() sql = 'INSERT INTO student(id,name,age) values(%s,%s,%s)' cursor.execute(sql,(id,name,age)) db.commit() except: db.rollback() db.close() #这里执行的SQL语句需要执行db对象的commit()方法才可实现数据插入。 #对于数据的插入更新删除操作，都需要调用commit方法才能生效。 #加入异常处理，如果执行失败，调用rollback()执行数据回滚，相当于nothing happened #这里涉及事务问题。事务机制可以确保数据的一致性。 #也就是这件事要么发生，要么没有发生。比如插入一条数据不会存在插入一半的情况。 #要么全部插入，要么都不插入，这就是事务的原子性（atomicity）。另外，事务还有3个属性 #一致性(consistency)#隔离性(isolation)#持久性(durability)#这四个属性通常称为ACID特性 #插入，更新，删除，都是对数据库进行更改的操作，而更改操作都必须为一个事务。 #这些操作的标准写法如下： # try: # cursor.execute(sql) # db.commit() # except: # db.rollback() #上面数据插入操作显然有不方便的地方，如果需要增加gender性别字段 #不仅sql语句需要改动，相应的元组参数也需要改成（id,name,age,gender） #所以很多情况下，我们会做成一个通用方法，只需要传入一个动态变化的字典就好了。 #然后SQL语句会根据字典动态构造 db = pymysql.connect(host='localhost',user='root',db='spiders') cursor = db.cursor() data = {'id':'20172011', 'name':'hahally', 'age':20 table = 'student' keys =','.join(data.keys()) values =','.join(['%s']*len(data)) sql ='INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table,keys=keys,values=values) if cursor.execute(sql,tuple(data.values())): print('Successful') db.commit() except: print('Failed') db.rollback() db.close() #首先，需要构造插入的字段id,name,age。这里只需要将data的键名取出，然后用逗号分开即可。 #然后需要构造多个%s当作占位符。 #最后利用字符串的format()方法将表名，字段名和占位符构造出来。 #注意：每次运行插入的数据不能相同，比如，上面的语句运行一次成功后 #再run第二次就会打印filed# #第三部分#更新数据# #在实际的数据抓取过程中，大部分情况下需要插入数据，但是我们关心的是会不会出现重复数据 #如果出现了，我们希望更新数据而不是重复保存一次。另外，就像前面所说的动态构造sql #这里我们还需要加入一种去重的方法，如果数据存在，则更新数据，如果不存在，则插入数据 db = pymysql.connect(host='localhost',user='root',db='spiders') cursor = db.cursor() data = {'id':'20172011xxx', 'name':'hahally', 'age':22 table = 'student' keys = ','.join(data.keys()) values = ','.join(['%s']*len(data)) sql ='INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table,keys=keys,values=values) update = ','.join([" {key}=%s".format(key=key) for key in data]) #注意{key}前面有个空格# sql +=update if cursor.execute(sql,tuple(data.values())*2): print('Successful') db.commit() else: print('要插入的数据和已保存的数据完全相同') except: print('Failed') db.rollback() db.close() #实现主键不存在便插入数据，存在则更新数据的功能# #第四部分#查询数据# db = pymysql.connect(host='localhost',user='root',db='spiders') cursor = db.cursor() sql = 'SELECT * FROM student WHERE age>=18' #查询age18及以上的学生 cursor.execute(sql) print('Count:',cursor.rowcount) #调用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少一条 #这是因为fetchall()方法的内部实现有一个偏移指针用来指向查询结果 #最开始偏移指针指向第一条数据，取一次之后，指针偏移就指向下一条数据了 #最开始我们调用了一次fetchone()方法，这样结果的偏移指针就指向下一条数据 #fetchall()方法返回的就是偏移指针指向的数据一直到结束的所有数据 #此外，我们还可以用while循环加fetchone()方法来获得所有数据 #fetchall()方法会以元组形式返回全部结果，如果数据量过大，占用的开销会非常高。 #so，一般推荐下面的方法逐条取数据 #sql = 'SELECT * FROM student WHERE age>=18' # try: # cursor.execute(sql) # print('Count:',cursor.rowcount) # row = cursor.fetchone() # while row: # print('Row:',row) # row = cursor.fetchone() # except: # print('error') # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #第五部分#删除数据# db = pymysql.connect(host='localhost',user='root',db='spiders') cursor = db.cursor() table='student' condition = 'age>20' #删除条件 sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table,condition=condition) cursor.execute(sql) db.commit() print('Successful')