MySQL操作

连接数据库

import pymysql

db = pymysql.connect(host='localhost',user='root',passwd='123456',port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
# 获取MySQL的版本号
data = cursor.fetchone()
# 创建数据库 spiders ，默认编码UTF-8
cursor.execute('CREATE DATABASE spiders DEFAULT CHARACTER SET utf8mb4')
db.close()

创建数据表

import pymysql

db = pymysql.connect(host='localhost', user='root', passwd='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()

插入数据

import pymysql

id = '2019210050'
user = 'wiselisx'
age = 22

db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='spiders' )
cursor = db.cursor()
# 插入数据
sql = 'INSERT INTO students (id, name, age) values(%ss, %ss, %ss)'
try:
    cursor.execute(sql,(id, user, age))
    db.commit()
except:
    db.close()
db.close()

import pymysql

data = {
    'id' : '2019210050',
    'user' : 'wiselisx',
    'age' : 22
}
db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='spiders' )
cursor = db.cursor()
# 插入数据
table = 'student'
keys = ','.join(data.keys())
values = ','.join(['%s'] * len(data))
sql = 'INSERT INTO {tavle} ({keys}) VALUES ({values})'.format(table=table, keys=keys, value=values)
try:
    if cursor.execute(sql,tuple(data.values)):
        print('Successful')
        db.commit()
except:
    print('Failed')
    db.rollback()
db.close()

更新数据

import pymysql

data = {
    'id' : '2019210050',
    'user' : 'wiselisx',
    'age' : 22
}
db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='spiders' )
cursor = db.cursor()
# 插入数据
table = 'student'
keys = ','.join(data.keys())
values = ','.join(['%s'] * len(data))
# 主键存在便更新数据，不存在则插入数据
sql = 'INSERT INTO {tavle} ({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table, keys=keys, value=values)
update = ','.join(['{KEY} = %s'.format(key=key) for key in data])
sql += update
try:
    if cursor.execute(sql,tuple(data.values)):
        print('Successful')
        db.commit()
except:
    print('Failed')
    db.rollback()
db.close()

删除数据

import pymysql

data = {
    'id' : '2019210050',
    'user' : 'wiselisx',
    'age' : 22
}
db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='spiders' )
cursor = db.cursor()
# 插入数据
table = 'student'
condaition = 'age>20'
# 删除数据
sql = 'DELETE FROM  {table} WHERE {condition}'.format(table=table, condition=condaition)
try:
    if cursor.execute(sql,tuple(data.values)):
        print('Successful')
        db.commit()
except:
    print('Failed')
    db.rollback()
db.close()

查询数据

import pymysql

data = {
    'id' : '2019210050',
    'user' : 'wiselisx',
    'age' : 22
}
db = pymysql.connect(host='localhost', user='root', passwd='123456', port=3306, db='spiders' )
cursor = db.cursor()
# 插入数据
table = 'student'
condaition = 'age>20'
# 删除数据
sql = 'SELECT * FROM  {table} WHERE {condition}'.format(table=table, condition=condaition)
try:
    if cursor.execute(sql,tuple(data.values)):
        print('Successful')
        db.commit()
except:
    print('Failed')
    db.rollback()
db.close()