Python 操作 MySQL数据库

 #!/usr/bin/python # coding=utf-8 import pymysql # 连接本地数据库 conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='yourpwd', db='samp_db1', charset='utf8') cursor = conn.cursor() cursor.execute('select * from bigstu') for row in cursor.fetchall(): print(row) # 查 cursor.execute('select id, name from bigstu where age > 22') for res in cursor.fetchall(): print(str(res[0]) + ", " + res[1]) cursor.close() print('-- end --')

 SELECT * FROM anindex.subject_basic_table where season_id having '2018';

 insertSql = "insert into bigstu (name, sex, age, mobile) values ('%s','%s',%d,'%s') " xiuji = ('秀吉', '男', 15, '13400001111') cursor.execute(insertSql % xiuji) conn.commit() # 别忘了提交

 addCo = "alter table bigstu add cash int after mobile" cursor.execute(addCo)

 addCo = "alter table bigstu add cash int default 0 after mobile" cursor.execute(addCo)

 deleteSql = "delete from bigstu where name = '%s'" cursor.execute(deleteSql % '秀吉')

 dropCo = "alter table bigstu drop cash" cursor.execute(dropCo)

 updateSql = "update bigstu set sex = '%s' where name = '%s'" updateXiuji = ('秀吉', '秀吉') # 秀吉的性别是秀吉 cursor.execute(updateSql % updateXiuji) conn.commit()

 table = "bigstu" addCash = "update " + table + " set cash = cash + '%d' where name = '%s'" lucky = (1000, "秀吉") try: cursor.execute(addCash % lucky) except Exception as e: conn.rollback() print("加钱失败了") else: conn.commit()

 import pymysql import json import os import secureUtils mapping_keys = json.load(open("key_mapping_db.json", "r")) db_keys = [] # json中所有的key for k in mapping_keys.values(): db_keys.append(k) conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='*****', db='db_name', charset='utf8') cursor = conn.cursor() table_main = "table_main" main_table_keys = [] # 主表的列名 cursor.execute("show columns from " + table_main) for row in cursor.fetchall(): main_table_keys.append(row[0]) staff_table_keys = [] cursor.execute("show columns from table_second") for row in cursor.fetchall(): staff_table_keys.append(row[0]) need_to_insert_keys = [] for k in db_keys: if k not in staff_table_keys and k not in main_table_keys and k not in need_to_insert_keys: need_to_insert_keys.append(k) print("need to insert " + str(len(need_to_insert_keys))) print(need_to_insert_keys) for kn in need_to_insert_keys: print("add key to db " + kn) cursor.execute("alter table staff_table add " + kn +" text") conn.close()

 # change column character set to utf8 for co in main_table_keys: change_sql = "alter table " + table_main + " modify " + co + " text character set utf8" print(change_sql) cursor.execute(change_sql)