python连接mysql，进行增删改查基本操作

1.先安装mysql模块  pip install pymysql

2.import mysql

3.创建一张表

pymysql.paramstyle='format'
db=pymysql.connect("localhost","root","A","test")
cursor=db.cursor()
cursor

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

 

4.添加一条数据

db=pymysql.connect("localhost","root","A","test")
cursor=db.cursor()
sql="""INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME) VALUES ('MAC','Mohan',20,'M',2000)"""
try:
    cursor.execute(sql)
    db.commit()
    print('添加成功')
except:
    db.rollback()
    print('添加失败')
db.close()

#2.用占位符
db=pymysql.connect("localhost","root","A","test")
cursor=db.cursor()
sql="""INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME) VALUES ('%s','%s','%s','%s','%s')"""
try:
    cursor.execute(sql %('shi','peng','20','M','2000'))
    db.commit()
    print('添加成功')
except:
    db.rollback()
    print('添加失败')
db.close()

5.查询数据

#查看所有数据
db=pymysql.connect("localhost","root","A","test")
cursor=db.cursor()
sql="SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" %(1000)
try:
    cursor.execute(sql)
    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))
except Exception as e:
    print(e)
db.close()

6.修改一条数据

#更新数据
db=pymysql.connect("localhost","root","A","test")
cursor=db.cursor()
sql="""UPDATE EMPLOYEE SET AGE=AGE+1 WHERE SEX= '%c'""" %('M')
try:
    cursor.execute(sql)
    db.commit()
    print('修改成功')
except:
    db.rollback()
    print('修改失败')
db.close()

7.删除一条

#删除一条
db=pymysql.connect("localhost","root","A","test")
cursor=db.cursor()
sql="""delete from employee where age<%s"""
try:
    cursor.execute(sql,'30')
    db.commit()
    print('删除成功')
except:
    db.rollback()
    print('失败')
db.close()

8.高级用法

#定义上下文管理器，连接后自动关闭连接
class mysql:
    def __init__(self,host='localhost',port=3306,user='root',passwd='A',db='test',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 容器
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))

#自定义上下文管理器，连接后自动关闭连接
import contextlib

@contextlib.contextmanager
def mysql(host='localhost',port=3306,user='root',passwd='A',db='test',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()
        
#执行 with 容器
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))