# ！/usr/bin/python3
# -*- coding: utf-8 -*-
import pymysql
OPTION = {
"check_sql_data": "0",
"create_tables": "1",
"add_sql_data": "2",
"put_sql_data": "3",
}
def __get_connect():
# 连接数据库
connect = pymysql.Connect(
host=host,
port=port,
user=user,
passwd=‘password‘,
db="db_name",
charset=‘utf8‘
)
return connect
def create_tables():
tables = "5"
# 创建数据库表
create_table = """
create table my{}(
id int unsigned primary key auto_increment,
first_name char(10) not null,
last_name char(10) not null,
age int unsigned,
sex tinyint,
money float
)
""".format(tables)
try:
connect = __get_connect()
cursor = connect.cursor()
cursor.execute(create_table)
except Exception as e:
connect.rollback() # 事务回滚
print(‘事务处理失败‘, e)
else:
connect.commit() # 事务提交
print(‘事务处理成功‘, cursor.rowcount)
# 关闭连接
cursor.close()
connect.close()
def check_sql_data():
# 数据库查询
check_sql = "select * from my23"
try:
connect = __get_connect()
cursor = connect.cursor()
cursor.execute(check_sql)
data = cursor.fetchmany(11)
except Exception as e:
connect.rollback() # 事务回滚
print(‘数据查询失败‘, e)
return "数据查询失败"
else:
connect.commit() # 事务提交
print("查询到{}条数据," .format(cursor.rowcount), data)
# 关闭连接
cursor.close()
connect.close()
def add_sql_data():
# 往数据库表增加数据
add_sql = "insert into myt033(first_name,last_name,age,sex,money) values(%s,%s,%s,%s,%s)"
try:
connect = __get_connect()
cursor = connect.cursor()
cursor.executemany(add_sql, [("Liu", "Mick", 90, 1, 9.9), ("L", "M", 12, 3, 44)])
except Exception as e:
connect.rollback() # 事务回滚
print(‘数据新增失败‘, e)
else:
connect.commit() # 事务提交
print("新增{}数据成功".format(cursor.rowcount))
# 关闭连接
cursor.close()
connect.close()
def put_sql_data():
# 修改数据库数据
put_sql = "update my23 set first_name = %s where id = %s"
try:
connect = __get_connect()
cursor = connect.cursor()
cursor.executemany(put_sql, [("test11", 1), ("test211", 2)])
print("修改{}条数据成功".format(cursor.rowcount))
except Exception as e:
connect.rollback() # 事务回滚
print(‘数据修改失败‘, e)
else:
connect.commit() # 事务提交
print("{}条数据成功".format(cursor.rowcount))
# 关闭连接
cursor.close()
connect.close()
def option_in(arg1):
if arg1 == OPTION.get("check_sql_data"):
check_sql_data()
elif arg1 == OPTION.get("create_tables"):
create_tables()
elif arg1 == OPTION.get("add_sql_data"):
add_sql_data()
elif arg1 == OPTION.get("put_sql_data"):
put_sql_data()
else:
print("您输入的参数无效,请输入数字, 只支持0~3")
if __name__ == "__main__":
arg = input("请输入：")
option_in(arg)
# python3交流群: 305357273
python3结合pymysql模块对数据库进行建表，增, 改，查