# !/usr/bin/python3
# -*- coding: utf-8 -*-
# Author: 刘小懒
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模块对数据库进行建表,增, 改,查
原创
©著作权归作者所有:来自51CTO博客作者刘小潭的原创作品,请联系作者获取转载授权,否则将追究法律责任
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
pymysql实现数据库的增、删、改、查
python数据库的操作,pymysql
数据 mysql 提交事务 pymysql -
uniCloud 云数据库(新建表、增、删、改、查)
todo 为自定义的表名表结构描述文件的默认后缀为。
uniCloud 云数据库 数据 字段 bc -
Python 操作 pymysql 批量 增、删、改、查
github:https://github.
mysql sql 数据库连接 -
Mysql数据库的增、删、改、查
Mysql基础命令 增删改查
Mysql Linux