Python 操作 MySQL数据库(2)-木庄网络博客

更新符合条件的数据

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

事物处理

给某个记录的cash增加

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()

直接执行SQL语句，十分方便

代码片段

给数据库添加列

从json中读取需要添加的列名，获取当前2个表中所有的列名
整理得出需要插入的列名，然后将列插入到相应的表中

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()

将字段字符改变

这里将main_table_keys中的所有字段改为utf8

# 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)

以上就是Python 如何操作 MySQL的详细内容，更多关于Python 操作 MySQL的资料请关注其它相关文章！

更多相关Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容