一、创建数据库

import pymysql
conn = pymysql.connect(host='ip', user='root', password='密码')

以字典的形式返回操作结果

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "create database db1 default charset utf8"
cursor.execute(sql)
cursor.close()
conn.close()

二、创建表

import pymysql
conn = pymysql.connect(host='ip', user='root', password='密码', db='db1')

以字典的形式返回操作结果

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = """
create table students(
id varchar(20),
name varchar(20),
age int
)engine=innodb default charset=utf8
"""
cursor.execute(sql)
cursor.close()
conn.close()

注意：创建表时，要设置主键

create table students(
id varchar(20) primary key,
name varchar(20),
age int
)engine=innodb default charset=utf8;

三、操作数据

1、插入数据

a、普通版

import pymysql
conn = pymysql.connect(host='ip', user='root', password='密码', db='db1')

以字典的形式返回操作结果

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
num = ''
name = 'ajax'
age = 25
sql = "insert into students(id, name, age) values(%s, %s, %s)"
try:
cursor.execute(sql, (num, name, age))
# 插入数据，需要提交连接mysql对象
conn.commit()
except Exception as e:
print(e)
conn.rollback()
cursor.close()
conn.close()

注意：conn.commit()， 支持事务(innodb)

b、高级版(灵活版)

import pymysql
conn = pymysql.connect(host='ip', user='root', password='密码', db='db1')

以字典的形式返回操作结果

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
data = {
'id': '',
'name': 'tom',
'age': 24
}

table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data.values()))

print(keys)

print(values)

sql = "insert into {table}({keys}) values({values})".format(table=table, keys=keys, values=values)

print(sql)

print(tuple(data.values()))

try:
cursor.execute(sql, tuple(data.values()))
# 插入数据，需要提交连接mysql对象
print('数据添加成功')
conn.commit()
except Exception as e:
print(e)
conn.rollback()
cursor.close()
conn.close()

2、更新数据

a、普通版

import pymysql
conn = pymysql.connect(host='ip', user='root', password='密码', db='db1')

以字典的形式返回操作结果

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
age = 100
id = ''

sql = "update students set age=%s where id=%s"

try:
cursor.execute(sql, (age, id))
print('修改数据成功')
conn.commit()
except Exception as e:
print(e)
conn.rollback()

b、高级版(灵活版)

作用：如果数据存在，则更新数据，如果数据不存在，则添加数据

import pymysql

conn = pymysql.connect(host='ip', user='root', password='密码', db='db1')

以字典的形式返回操作结果

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
data = {
'id': '',
'name': 'tom',
'age': 26
}

table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data.values()))

print(keys)

print(values)

sql = "insert into {table}({keys}) values({values}) on duplicate key update".format(table=table, keys=keys,
values=values)

print(sql)

update = ', '.join([" {}= %s".format(key) for key in data])

print(update)

sql = sql + update

print(sql)

insert into students(id, name, age) values(%s, %s, %s) on duplicate key update id= %s, name= %s, age= %s

print(tuple(data.values()) * 2)

try:
cursor.execute(sql, tuple(data.values()) * 2)
# 插入数据，需要提交连接mysql对象
print('数据更新或添加成功')
conn.commit()
except Exception as e:
print(e)
conn.rollback()
cursor.close()
conn.close()

3、删除数据

import pymysql

conn = pymysql.connect(host='ip', user='root', password='密码', db='db1')

以字典的形式返回操作结果

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

table = 'students'
condition = 'age > 30'
sql = "delete from {table} where {condition}".format(table=table, condition=condition)

print(sql)

try:
cursor.execute(sql)
print('删除数据成功')
conn.commit()
except Exception as e:
print(e)
conn.rollback()

4、查询数据

import pymysql

conn = pymysql.connect(host='ip', user='root', password='密码', db='db1')

以字典的形式返回操作结果

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

table = 'students'
condition = 'age > 10'
sql = "select * from {table} where {condition}".format(table=table, condition=condition)

print(sql)

try:
cursor.execute(sql)
results = cursor.fetchall()
print(results)
except Exception as e:
conn.rollback()