Pyhton爬虫学习笔记（十二）pymysql

import pymysql
############################# 创建数据库
db = pymysql.connect(host='localhost', user='root', password='123', port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
data = cursor.fetchone()
print('Database Vsersion: ',data)
cursor.execute('CREATE DATABASE spiders DEFAULT CHARACTER set utf8')
db.close()

############################ 创建表
db = pymysql.connect(host='localhost',user='root',password='123',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()

############################################# 向表中插入数据
id='002'
name='liyun'
age=20

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

###############################################动态构造插入语句
db = pymysql.connect(host='localhost',user='root',password='123',port=3306,db='spiders')
cursor = db.cursor()
data={
    'id':'003',
    'name':'liyun',
    'age':20
}
table = 'students'
keys = ','.join(data.keys())
print(data.keys())
print(keys)
values = ','.join(['%s']*len(data))
print(values)

sql= 'INSERT INTO {table}({keys}) values({values}) '.format(table=table,keys=keys,values=values)
try:
    if cursor.execute(sql,tuple(data.values())):
        print('success')
        db.commit()
except Exception as e:
    print(e)
    db.rollback()
db.close()

#################################################################################
#####  动态更新数据 实现去重 如果数据已存在就更新 如果不存在则插入
#####   ON DUPLICATE KEY UPDATE  仅在mysql下有用
db  = pymysql.connect(host = 'localhost',user='root',password='123',port=3306,db='spiders')
cursor = db.cursor()
data = {'id':'004',
        'name':'zhangyun',
        'age':22
             }
table = 'students'
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)
sql += update
####完整的sql语句  insert into students(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)
    print('successful')
    db.commit()
except Exception as e:
    print(e)
    db.rollback()
db.close()


###################################################################
###删除数据
db = pymysql.connect(host='localhost',user='root',password='123',port=3306,db='spiders')
cursor = db.cursor()
table='students'
name='NANA'
condition = 'name="%s"'%name
sql  = 'DELETE FROM {table} where {condition}'.format(table=table,condition=condition)
try:
    cursor.execute(sql)
    print('successful')
    db.commit()
except Exception as e:
    print(e)
    db.rollback()
db.close()


######################################################查询
db = pymysql.connect(host='localhost',user='root',password='123',port=3306,db='spiders')
cursor = db.cursor()
table = 'students'
condition = 'age>1'
sql = 'SELECT * FROM {table} where {condition}'.format(table=table,condition=condition)
try:
    # cursor.execute(sql)  ###查询语句不用再db.commit()
    # print('RowCount ;', cursor.rowcount)
    # print('One : ',cursor.fetchone())
    # print('Results : ',cursor.fetchall())
    cursor.execute(sql)
    row =  cursor.fetchone()
    while row:
        print('Row : ',row)
        row = cursor.fetchone()  #内部有一个偏移指针 取一次后 指针偏移到下一个位置
except Exception as e:
    print(e)
db.close()