pymysql总结

一、创建数据库

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 = '10002'
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': '1001',
    '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 = '1001'

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': '1003',
    '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()