关系型数据库存储

关系型数据库存储

mysql的存储

• 准备

  安装pymysql库。

  安装mysql并运行。

连接数据库

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306)
#通过pymysql的connect()方法声明一个mysql的连接对象db,传入mysql的运行ip，用户，密码,端口
cursor=db.cursor()#调用cursor()方法过的mysql的操作游标，利用游标来执行sql语句
cursor.execute('select version()')#查询版本号
data=cursor.fetchone()
print('Data version:',data)
cursor.execute("create database spiders ")#创建spiders数据库
db.close()
'''
Data version: ('5.5.53',)
<class 'tuple'>
'''

创建表

import pymysql

db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='spiders')
cursor=db.cursor()
sql='create table stu(id varchar(20) primary key,name varchar(20) not null,age int not null)'
cursor.execute(sql)
row=cursor.fetchall()
db.close()
print(row)

插入数据

import pymysql

db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='spiders')
cursor=db.cursor()
id='1002'
name='Tom'
age=23
sql='insert into stu values(%s,%s,%s)'#字符串拼接过于繁琐，使用格式化字符%s来实现，有几个value写几个%s
#标准写法
try:
    cursor.execute(sql,(id,name,age))#values的值用统一的元组传入
    db.commit()#执行db对象的commit()实现插入，这个方法才是将sql语句提交到数据库执行的方法
except:
    db.rollback()
db.close()

动态构造SQL插入

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='spiders')
data={
    'id':'1005',
    'name':'Mike',
    'age':20
}
cursor=db.cursor()
table='stu'
keys=', '.join(data.keys())#将字典类型的数据转为以,分隔的字符串---id,name,age
# print(keys)# id,name,age
values=', '.join(['%s']*len(data))
#构造占位符，有几个字段就构造几个占位符，首先定义长度为1的数组['%s']，然后扩充，join()对其进行格式化为以,分隔的字符串
#print(values)  # %s,%s,%s
sql='insert into {table}({keys}) values({values})'.format(table=table,keys=keys,values=values)
#format()方法通过{}来实现格式化输出，这里用通过变量进行填充
print(sql)# insert into stu(name,sex,age) values(%s,%s,%s)
try:
    cursor.execute(sql,tuple(data.values()))#tuple()将字典类型的数据变为元组类型
    #print(tuple(data.values()))

    db.commit()
    print('insert Successfully')
except:
    print(cursor.fetchone())
    db.rollback()
    print('Failed')
db.close()

更新数据

最简单的就是构造一个SQL语句

sql='update stu set age=%s where name= %s'
try:
    cursor.execute(sql,(12,Tom))
    db.commit()
except:
    db.rollback()
db.close()

实际数据抓取过程中，大部分情况下要插入数据。当数据出现重复时，我们希望更新数据而不是重复保存。

实现去重的方法，若数据存在，则更新数据，否则，就插入数据。

import pymysql
data={
    'id':'1005',
    'name':'Mike',
    'age':120
}
db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='spiders')
cursor=db.cursor()
table='stu'
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)
#on duplicate key update 如果主键存在就执行更新操作

update =','.join([" {key} = %s".format(key=key) for key in data])# [' id = %s', ' name = %s', ' age = %s']
# print([" {key} = %s".format(key=key) for key in data])#[' id = %s', ' name = %s', ' age = %s']
#print(update) #id = %s,name = %s,age = %s
sql+=update
print(sql)#insert into stu(id,name,age) values(%s,%s,%s) on duplicate key update id = %s,name = %s,age = %s
try:
    cursor.execute(sql,tuple(data.values())*2)#  6个%s，tuple()元组*2变为原来的2倍
    db.commit()
    print('success')
except:
    print('failed')
    cursor.Error
    db.rollback()
db.close()

删除数据

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='spiders')
cursor=db.cursor()
condition='age>100'
table='stu'
sql='delete from {table} where {condition}'.format(table=table,condition=condition)
print(sql)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

查询数据

import pymysql
db=pymysql.connect(host='localhost',user='root',password='root',port=3306,db='spiders')
cursor=db.cursor()
condition='age<=23'
table='stu'
sql='select * from {table} where {condition}'.format(table=table,condition=condition)
print(sql)
try:
    cursor.execute(sql)
    print('count:',cursor.rowcount)
    one=cursor.fetchone()
    print('one:',one)
    results=cursor.fetchall()
    #fetchall()获取的数据是3条而不是4条，由于前面调用了fetchone()方法，使其内部的指针偏移到下一条数据
    print('results:',results)
    print('Type results:',type(results))
    for row in results:
        print(row)
except:
    print('failed')
db.close()

'''
select * from stu where age>=10
count: 4
one: ('1002', 'Tom', 23)
results: (('1003', 'Mary', 20), ('1006', 'Bob', 25), ('10023', 'Mike', 25))
Type results: <class 'tuple'>
('1003', 'Mary', 20)
('1006', 'Bob', 25)
('10023', 'Mike', 25)
'''

由于数据量很大时，fetchall()将结果以元组的形式返回占用的开销高，所以建议逐条取数据。

try:
    cursor.execute(sql)
    print('count:',cursor.rowcount)
    row=cursor.fetchone()
    while row:
        print("Row:",row)
        row=cursor.fetchone()
except:
    print('failed')
db.close()