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