Python中关于数据库的增删改查

增删改查

首先要准备数据库MySQL的安装，确保安装能用。然后安装 PyMySQL库
先尝试链接数据库，测试的是本地数据库，用户名root，密码1523456，远行端口默认3306。用pymysql先链接MySQL，在创建一个名为spiders的数据库

import pymysql

# 创建数据库spiders和创建表单
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
cursor.execute('CREATE DATABASE spiders DEFAULT CHARACTER SET utf8')
# db.close()
db = pymysql.connect(host='localhost', user='root', password='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)

# 增
try:
    cursor.execute('insert into students(id, name, age) values(%s, %s, %s)', ("201810159235", "独角兽", 20))
    db.commit()
except:
    db.rollback()
# db.close()
# 用字典增加元素
data = {
    'id': '201810159234',
    'name': 'bobo',
    'age': 20
}
table = 'students'
keys = ','.join(data.keys())
values = ','.join(['%s']*len(data))
try:
    if cursor.execute(
      'insert into {table}({keys}) values({values})' .format(table=table, keys=keys, values=values),
        tuple(data.values())
    ):
        print('Successful')
        db.commit()
except:
    print("Failed")
    db.rollback()


# 改
sql = 'update students set age = %s where name = %s'
try:
    cursor.execute(sql, (22, 'bobo'))
    db.commit()
except:
    db.rollback()

# 查
sql = 'select * from students where age >= 20'
try:
    cursor.execute(sql)
    results = cursor.fetchall()
    print(results)
except:
    print('error')

# 删
table = 'students'
condition = 'age > 20'
sql = 'delete from {table} where {condition}'.format(table=table, condition=condition)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()