Python操作使用MySQL資料庫的執行個體代碼，pythonmysql

Python操作使用MySQL資料庫的執行個體代碼，pythonmysql

Python 操作 MySQL

配置

1. win_64
2. Ubuntu14.04
3. Python3.x
   

pip安裝pymysql模組

直接使用pip安裝 pip install pymysql

win64上直接在cmd中執行

串連本機資料庫

使用模組pymysql串連資料庫

#!/usr/bin/python# coding=utf-8import pymysql# 串連本機資料庫conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='a123', 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 --')

輸出：

(1, '張三', '男', 24, datetime.date(2017, 3, 29), '13666665555')(6, '小剛', '男', 23, datetime.date(2017, 3, 11), '778899888')(8, '小霞', '女', 20, datetime.date(2017, 3, 13), '13712345678')(12, '小智', '男', 21, datetime.date(2017, 3, 7), '13787654321')1, 張三6, 小剛-- end --

可以直接執行sql語句。獲得的結果是元組。

增

插入資料

插入一條資料，接上面的代碼

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

添加列

在mobile後面添加一列cash

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)

刪

刪除資料

刪除 name=秀吉 的資料

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

刪除列

刪除cash列

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

事物處理

給某個記錄的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 pymysqlimport jsonimport osimport secureUtilsmapping_keys = json.load(open("key_mapping_db.json", "r"))db_keys = [] # json中所有的keyfor 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 utf8for co in main_table_keys:  change_sql = "alter table " + table_main + " modify " + co + " text character set utf8"  print(change_sql)  cursor.execute(change_sql)

以上就是本文的全部內容，希望對大家的學習有所協助，也希望大家多多支援幫客之家。