一、首先需要安裝包pymysql(python3所對應(yīng))

我使用的是Anaconda全家桶，打開cmd，進入Anaconda下的Scripts文件夾下輸入命令：pip install pymysql進行下載安裝

二、我使用的編譯器為Anaconda所帶的Jupyter Notebook

1，在mysql中創(chuàng)建一個名稱為drop的數(shù)據(jù)庫，當然里面不存在表

2，導(dǎo)包和配置數(shù)據(jù)庫信息

import pymysql DBHOST = 'localhost' DBUSER = 'root' DBPASS = 'beyond' DBNAME = 'drop' DBSET = 'utf8'

三、在drop數(shù)據(jù)庫中創(chuàng)建water表

try:conn = pymysql.connect(host = DBHOST,user = DBUSER,password= DBPASS,database= DBNAME,charset= DBSET)#連接drop這個數(shù)據(jù)庫print('seccessfull!!!')cur = conn.cursor()cur.execute("DROP TABLE IF EXISTS water")#創(chuàng)建water表之前先檢查是否存在這個表，若存在則刪除sql = "CREATE TABLE water(id int primary key NOT NULL AUTO_INCREMENT, circum varchar(8), area varchar(8), diameter varchar(8), PH varchar(8))"#創(chuàng)建表，其中id為主鍵、自增、不為空cur.execute(sql)print('create table seccess!!!')except pymysql.Error as e:print('table create is defeated!' + str(e))

四、向water表中插入數(shù)據(jù)

try:conn = pymysql.connect(host = DBHOST,user = DBUSER,password= DBPASS,database= DBNAME,charset= DBSET)#連接drop這個數(shù)據(jù)庫print('seccessfull!!!')cur = conn.cursor()sql = "INSERT INTO water(circum,area,diameter,PH) VALUE (%s,%s,%s,%s)"#向表中插入數(shù)據(jù)value = ('12.54','124.121','147.25',6)cur.execute(sql,value)conn.commit()print('insert seccess!!!')except pymysql.Error as e:print('insert is defeated!' + str(e))conn.rollback()conn.close()

為了后續(xù)操作，這里多執(zhí)行幾次，多增加幾組數(shù)據(jù)

五、查詢water表中數(shù)據(jù)

try:conn = pymysql.connect(host = DBHOST,user = DBUSER,password= DBPASS,database= DBNAME,charset= DBSET)#連接drop這個數(shù)據(jù)庫print('seccessfull!!!')cur = conn.cursor()sql = "SELECT * FROM water"#查詢water表中數(shù)據(jù)信息cur.execute(sql)results = cur.fetchall()for row in results:#這里獲取的results是個數(shù)組，里面分別存放每列的數(shù)值circum = row[1]area = row[2]diameter = row[3]ph = row[4]print('circum:%s,area:%s,diameter:%s,ph:%s'%(circum,area,diameter,ph))except pymysql.Error as e:print('query is defeat!' + str(e))conn.rollback()conn.close()

六、更新water表中數(shù)據(jù)

try:conn = pymysql.connect(host = DBHOST,user = DBUSER,password= DBPASS,database= DBNAME,charset= DBSET)#連接python這個數(shù)據(jù)庫print('seccessfull!!!')cur = conn.cursor()sql = "UPDATE water SET circum=%s,area=%s,diameter=%s WHERE ph=%s"#更新water表中數(shù)據(jù)信息value = ('15.5','15.5','15.5','6')#在ph=6的數(shù)據(jù)中，更改數(shù)據(jù)其他信息cur.execute(sql,value)conn.commit()print('update seccess!')except pymysql.Error as e:print('update is defeat!' + str(e))conn.rollback()conn.close()

七、刪除water表中指定位置數(shù)據(jù)

try:conn = pymysql.connect(host = DBHOST,user = DBUSER,password= DBPASS,database= DBNAME,charset= DBSET)#連接drop這個數(shù)據(jù)庫print('seccessfull!!!')cur = conn.cursor()sql = "DELETE FROM water WHERE ph=%s"value = ('6')cur.execute(sql,value)#刪除water表中ph=6的數(shù)據(jù)信息conn.commit()print('delete seccess!')except pymysql.Error as e:print('delete is defeat!' + str(e))conn.rollback()conn.close()

八、刪除water表

try:conn = pymysql.connect(host = DBHOST,user = DBUSER,password= DBPASS,database= DBNAME,charset= DBSET)#連接drop這個數(shù)據(jù)庫print('seccessfull!!!')cur = conn.cursor()sql = "DROP TABLE IF EXISTS water"#刪除water表cur.execute(sql)conn.commit()print('table delete is seccessful!')except pymysql.Error as e:print('table delete is defeat!' + str(e))conn.rollback()conn.close()

總結(jié)

以上是生活随笔為你收集整理的Python连接MySQL及一系列相关操作的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。