python连接mysql

python连接mysql

要想使用python连接mysql我们首先需要按照anaconda3连接数据库的驱动
在电脑控制台输入以下命令自动下载安装到anaconda3的目录下

pip install pymysql

然后就可以在代码里导入mysql的包了

import pymysql
#查看版本
pymysql.apilevel
#查看参数类型
pymysql.paramstyle
#修改参数类型，默认为'pyformat'
pymysql.paramstyle='qmark'

import pymysql
#建立连接
db=pymysql.connect('127.0.0.1','root','a','bank')
print(db)
#获取游标
cursor=db.cursor()

#建表
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

sql="""CREATE TABLE EMPLOYEE(
        FIRST_NAME CHAR(20) NOT NULL,
        LAST_NAME CHAR(20),
        AGE INT,
        SEX CHAR(1),
        INCOME FLOAT)"""

cursor.execute(sql)
db.close()



#2.插入数据
db=pymysql.connect('127.0.0.1','root','a','bank')
cursor=db.cursor()
sql= """INSERT INTO EMPLOYEE (FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
        VALUES('Kylin','Mohan',21,'M',14000) """

try:
    cursor.execute(sql)
    db.commit()
    print('添加成功!')
except:
    db.rollback()
    print('添加失败!')
db.close()



#2.插入数据
db=pymysql.connect('127.0.0.1','root','a','bank')
cursor=db.cursor()
sql= """INSERT INTO EMPLOYEE (FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
        VALUES('%s','%s','%s','%s','%s') """

try:
    cursor.execute(sql % ('Jack','Mohan','22','M','15000'))
    db.commit()
    print('添加成功!')
except:
    db.rollback()
    print('添加失败!')
db.close()


#查所有数据
db=pymysql.connect('127.0.0.1','root','a','bank')
cursor=db.cursor()
sql="SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (14000)

try:
    cursor.execute(sql)
    result = cursor.fetchall()
    for row in result:
        fname=row[0]
        lname=row[1]
        age=row[2]
        sex=row[3]
        income=row[4]
        
        print('fname=%s,lname=%s,age=%d,sex=%s,income=%d ' %(fname,lname,age,sex,income))

except Exception as e:
    print('查询失败!',e)
db.close()


#3.用pymysql更新数据
db=pymysql.connect('127.0.0.1','root','a','bank')
cursor=db.cursor()
sql="UPDATE EMPLOYEE SET AGE = AGE+1 WHERE SEX = '%c'" %('M')
try:
    cursor.execute(sql)
    db.commit()
    print('修改成功!')    

except Exception as e:
    db.rollback()
    print('修改失败!',e)
db.close()



#删除
db=pymysql.connect('127.0.0.1','root','a','bank')
cursor=db.cursor()
sql="delete from EMPLOYEE where age<%s" 
try:
    cursor.execute(sql,'23')
    db.commit()
    print('删除成功!')    

except Exception as e:
    print('删除失败!',e)
    db.rollback()
    
db.close()

以上就是pymysql的最基本用法，但是每用一次pymysql都要开关一次非常的麻烦，所以下面我们使用容器来帮我们管理

import pymysql

#这是自定义的，利用了class的生命周期
class mysql:
    def __init__(self,host='127.0.0.1',port=3306,user='root',passwd='a',db='bank',charset='utf8'):
        self.conn=pymysql.connect(host=host,port=port,user=user,passwd=passwd,db=db,charset=charset)
    def __enter__(self):
        self.cursor=self.conn.cursor()
        return self.cursor
    def __exit__(self,exceptionType,exceptionVal,trace):
        self.conn.commit()
        self.cursor.close()
        self.conn.close
                
with mysql() as cursor:
    row_count=cursor.execute("select * from EMPLOYEE ")
    results=cursor.fetchall()
    for row in results:
        fname=row[0]
        lname=row[1]
        age=row[2]
        sex=row[3]
        income=row[4]        
        print('fname=%s,lname=%s,age=%d,sex=%s,income=%d ' %(fname,lname,age,sex,income))

#更高级: with -> 容器
import contextlib
#定义上下文管理器，连接后自动关闭连接
@contextlib.contextmanager
def mysql(host='127.0.0.1',port=3306,user='root',passwd='a',db='bank',charset='utf8'):
    conn = pymysql.connect(host=host,port=port,user=user,passwd=passwd,db=db,charset=charset)
    cursor =conn.cursor()
    try:
        yield cursor
    finally:
        conn.commit()
        cursor.close()
        conn.close()
        
# 执行sql
with mysql() as cursor:
    row_count=cursor.execute("select * from EMPLOYEE ")
    results=cursor.fetchall()
    for row in results:
        fname=row[0]
        lname=row[1]
        age=row[2]
        sex=row[3]
        income=row[4]        
        print('fname=%s,lname=%s,age=%d,sex=%s,income=%d ' %(fname,lname,age,sex,income))