Python连接MySQL及一系列相关操作

我使用的是Anaconda全家桶，打开cmd，进入Anaconda下的scripts文件夹下输入命令：pip install pymysql进行下载安装
l

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

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

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

为了后续操作，这里多执行几次，多增加几组数据

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

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

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

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