python3结合pymysql模块对数据库进行建表,增, 改,查

jhshanyu00 2020-04-20

# !/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

相关推荐