一、首先需要安裝包pymysql(python3所對應)
我使用的是Anaconda全家桶，打開cmd，進入Anaconda下的Scripts文件夾下輸入命令：pip install pymysql進行下載安裝
二、我使用的編譯器為Anaconda所帶的Jupyter Notebook
1，在mysql中創建一個名稱為drop的數據庫，當然里面不存在表
2，導包和配置數據庫信息
import pymysql
DBHOST = 'localhost'
DBUSER = 'root'
DBPASS = 'beyond'
DBNAME = 'drop'
DBSET = 'utf8'
三、在drop數據庫中創建water表
try:conn = pymysql.connect(host = DBHOST,user = DBUSER,password= DBPASS,database= DBNAME,charset= DBSET)#連接drop這個數據庫print('seccessfull!!!')cur = conn.cursor()cur.execute("DROP TABLE IF EXISTS water")#創建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))"#創建表，其中id為主鍵、自增、不為空cur.execute(sql)print('create table seccess!!!')except pymysql.Error as e:print('table create is defeated!' + str(e))
四、向water表中插入數據
try:conn = pymysql.connect(host = DBHOST,user = DBUSER,password= DBPASS,database= DBNAME,charset= DBSET)#連接drop這個數據庫print('seccessfull!!!')cur = conn.cursor()sql = "INSERT INTO water(circum,area,diameter,PH) VALUE (%s,%s,%s,%s)"#向表中插入數據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()
為了后續操作，這里多執行幾次，多增加幾組數據
五、查詢water表中數據
try:conn = pymysql.connect(host = DBHOST,user = DBUSER,password= DBPASS,database= DBNAME,charset= DBSET)#連接drop這個數據庫print('seccessfull!!!')cur = conn.cursor()sql = "SELECT * FROM water"#查詢water表中數據信息cur.execute(sql)results = cur.fetchall()for row in results:#這里獲取的results是個數組，里面分別存放每列的數值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表中數據
try:conn = pymysql.connect(host = DBHOST,user = DBUSER,password= DBPASS,database= DBNAME,charset= DBSET)#連接python這個數據庫print('seccessfull!!!')cur = conn.cursor()sql = "UPDATE water SET circum=%s,area=%s,diameter=%s WHERE ph=%s"#更新water表中數據信息value = ('15.5','15.5','15.5','6')#在ph=6的數據中，更改數據其他信息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表中指定位置數據
try:conn = pymysql.connect(host = DBHOST,user = DBUSER,password= DBPASS,database= DBNAME,charset= DBSET)#連接drop這個數據庫print('seccessfull!!!')cur = conn.cursor()sql = "DELETE FROM water WHERE ph=%s"value = ('6')cur.execute(sql,value)#刪除water表中ph=6的數據信息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這個數據庫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()
