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;

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()

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()

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()

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()

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()

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()