爬虫学习笔记4——关系型数据库MySQL

1.基本步骤

1.连接数据库
pymysql.connect(host,user,password,port,db)方法:如果连接远程数据库，host的值为远程主机的ip，如果是本机，则为localhost或127.0.0.1；user为数据库的用户名，password为密码，db为创建的数据库的名称。（而非数据库软件）

import pymysql

#连接数据库mysql
db = pymysql.connect(host='localhost',user='root',password='root',port=3306,db='spiders')

2.cursor= db.cursor()创建游标
cursor用来对数据库执行各种操作，一般为cursor.execute(sql)

#创建游标，用游标操作数据库
cursor = db.cursor()
#用游标执行数据库语句
cursor.execute('SELECT VERSION()')

3.用游标执行sql语句
根据数据库的原子性，当某项操作中途失败，之前的操作将全部取消。即回滚。需执行语句：db.rollback()

try:
	cursor.execute(sql)
	db.commit()
except:
	db.rollback()

4.关闭数据库

db.close()

2.操作数据库

1.创建数据库

#执行创建数据库语句
cursor.execute('CREATE DATABASE spiders DEFAULT CHARACTER SET utf8mb4')

2.创建表

#创建表
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)

3.向表中插入数据
当表发生变化时，应执行语句：
db.commit()

#向表中插入数据
id = '111111'
name = 'Kevin'
age = 23

sql = 'INSERT INTO students(id,name,age) values(%s,%s,%s)'
try:
	cursor.execute(sql,(id,name,age))
	db.commit()
except:
	db.rollback()

4.灵活动态得向表中插入数据，实际应用较多，比如新增gender属性
思路：将要插入的数据转为字典形式，将字典的keys作为属性，将字典的values作为值。

这里需要补充三个方法：
tuple()方法的作用是将括号中的数据类型转为元组tuple()

‘’.joi(),作用是将括号中的参数用单引号内的符号隔开；
‘str’.format(),单引号内的字符串中包含花括号，生成后的字符串是，括号中的变量或值放入花括号中
这两个方法具体可以学习：
join方法
.format方法

data = {'id':'1222111',
		'name':'Dabby',
		'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()

5.动态插入新数据是，当key已经存在，对该项数据执行更新操作

#动态插入新数据是，当key已经存在，对该项数据执行更新操作
data = {'id':'111111',
		'name':'Kevin',
		'age':30}
		
table = 'students'
keys = ','.join(data.keys())
values = ','.join(['%s'] * len(data))

#此处创建动态可变的sql语句，使用了''.join()函数，作用是：将括号中的参数用单引号
#中的符号隔开，参数是一个可迭代的数据类型。可以是列表，字典，数组，元组
#''.format()函数是将括号中的参数放入左边单引号内的对应中花括号中
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])
sql += update
try:
	#tuple()函数是将括号中的参数数据类型转为元组
	if cursor.execute(sql,tuple(data.values())*2):
		print('Successful')
		db.commit()
except:
	print('Failed')
	db.rollback()

6.删除表中数据

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

7.查询数据
注意：查询结果需要cursor.fetchone()方法一个一个获取，或者用cursor.fetchall()获取全部

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)