首先，需要安装mysql数据库，配置好几个重要参数（数据库名、主机号、端口号、用户名、密码、数据库密码，一般情况下使用utf-8编码，读者按照自己安装情况修改对应参数。
# -*- coding: utf-8 -*-
#python operate mysql database import pymysql #数据库名称 DATABASE_NAME = 'test' #host = 'localhost' or '172.0.0.1' HOST = 'localhost' #端口号 PORT = '3306' #用户名称 USER_NAME = 'root' #数据库密码 PASSWORD = '123456' #数据库编码 CHAR_SET = 'utf8' #初始化参数 def init(): global DATABASE_NAME DATABASE_NAME = 'test' global HOST HOST = 'localhost' global PORT PORT = '3306' global USER_NAME USER_NAME = 'root' global PASSWORD PASSWORD = '123456' global CHAR_SET CHAR_SET = 'utf8' #获取数据库连接 def get_conn(): init() return pymysql.connect(host = HOST, user = USER_NAME, passwd = PASSWORD, db = DATABASE_NAME, charset = CHAR_SET) #获取cursor def get_cursor(conn): return conn.cursor() #关闭连接 def conn_close(conn): if conn != None: conn.close() #关闭cursor def cursor_close(cursor): if cursor != None: cursor.close() #关闭所有 def close(cursor, conn): cursor_close(cursor) conn_close(conn) def drop_table(): sql = ''' drop table if exists student ''' conn = get_conn() cursor = get_cursor(conn) result = cursor.execute(sql) conn.commit() close(cursor, conn) return result #创建表 def create_table(): sql = ''' CREATE TABLE student ( num int(11) NOT NULL, name varchar(20) NOT NULL, age int(11) DEFAULT NULL, PRIMARY KEY (num) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ''' conn = get_conn() cursor = get_cursor(conn) result = cursor.execute(sql) conn.commit() close(cursor, conn) return result #输出表中所有学员信息 def query_table_all(table_name): if table_name != '': sql = 'select * from ' + table_name conn = get_conn() cur= get_cursor(conn) result = cur.execute(sql) data= cur.fetchall() print(data,result,len(data)) for row in data: print(row) close(cur, conn) else: print('table name is empty!') def query_table(): Selectstr=input("\n---------------------\n请选择查询项：1 学号；2 姓名\n") sql = 'select * from student ' if Selectstr=="1": strnum =input("请输入要查询同学的学号：\n") sql = 'select * from student where num=' + strnum elif Selectstr=="2": strname =input("请输入要查询同学的姓名：\n") sql = "select * from student where name='" +strname+"'" conn = get_conn() cursor = get_cursor(conn) result = cursor.execute(sql) if result<1: print("查询结果为空！") else: for row in cursor.fetchall(): print(row) close(cursor, conn) #插入数据 def insert_table_init(): sql = "insert into student(num, name, age) values(1,'Zhang',24),(2,'Zhang1',24)" conn = get_conn() cursor = get_cursor(conn) result = cursor.execute(sql) conn.commit() close(cursor, conn) return result def insert_table(): strnum =input("请输入添加同学的学号：\n") strname=input("请输入添加同学的姓名：\n") strage =input("请输入添加同学的年龄：\n") sql = "insert into student(num, name, age) values("+strnum+",'"+strname+"',"+strage+")" conn = get_conn() cursor = get_cursor(conn) result = cursor.execute(sql) conn.commit() close(cursor, conn) return result #更新数据 def update_table(): strnum =input("请输入更新同学的学号：\n") strname=input("请输入更新同学的姓名：\n") strage =input("请输入更新同学的年龄：\n") sql = "update student set name ='"+strname+"'"+",age="+strage+" where num = "+strnum conn = get_conn() cursor = get_cursor(conn) result = cursor.execute(sql) conn.commit() close(cursor, conn) return result #删除数据 def delete_data(): strnum=input("请输入需要删除同学的学号：\n") sql = 'delete from student where num = '+strnum conn = get_conn() cursor = get_cursor(conn) result = cursor.execute(sql) conn.commit() close(cursor, conn) return result #数据库连接信息 def print_info(): print('数据库连接信息:' + DATABASE_NAME + " "+HOST + " "+PORT +\ " "+USER_NAME +" "+PASSWORD +" "+CHAR_SET) 127 #打印出数据库中表情况 def show_databases(): sql = 'show databases' conn = get_conn() cursor = get_cursor(conn) result = cursor.execute(sql) for row in cursor.fetchall(): print(row) #数据库中表情况 def show_tables(): conn = get_conn() cursor = get_cursor(conn) sql = 'show databases' result = cursor.execute(sql) for row in cursor.fetchall(): print(row,end=' ') sql = 'use test' result = cursor.execute(sql) for row in cursor.fetchall(): print(row,end=' ') def main(): print("数据库连接信息如下：") print_info() print("当前数据库下的数据表为：") show_tables() #删除表 print("\n如果存在student表，下面将会删除！") result = drop_table() if result!=-1: print("操作成功!") #创建表 print("创建student表！") result = create_table() if result!=-1: print("\n操作成功!") #测试前线加入一条数据： result =insert_table_init() print("\n目前数据表中数据如下：") query_table_all('student') while True: Selectstr=input("\n---------------------\n请选择对数据表的操作：\n1 插入数据；2 更新数据 3 查询数据 4 删除数据 5 按q退出。\n") if Selectstr=="1": #插入数据 result =insert_table() if result!=-1: print("\n操作成功,插入数据后....") query_table_all('student') elif Selectstr=="2": #更新数据 result =update_table() if result!=-1: print("\n操作成功,更新数据后....") query_table_all('student') elif Selectstr=="3": #查询表 print("\n查询表student！") query_table() elif Selectstr=="4": #删除数据 delete_data() print('\n删除数据后....') query_table_all('student') elif Selectstr=="q": break print("\n谢谢使用,欢迎下次光临!") main()