Python 连接Mysql数据库并进行增删改查操作

与java操作数据库类似，首先需要导入驱动,需要下载驱动包

pip install pymysql

下载成功之后导入pymysql

import pymysql

这里测试一下是否成功导入

conn = pymysql.connect('localhost',user = "root",passwd = "a",db = "test")
print (conn)
print (type(conn))

输出 如下，则连接成功

<pymysql.connections.Connection object at 0x000001E496FA6688>
<class 'pymysql.connections.Connection'>

创建数据库

#创建pythonBD数据库
cursor.execute('CREATE DATABASE IF NOT EXISTS pythonDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')
cursor.close()#先关闭游标
conn.close()#再关闭数据库连接
print('创建pythonBD数据库成功')

 创建表

sql= '''CREATE TABLE ELPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
)
'''
cursor.execute(sql)

包装为类

#包装为类
class Mysql():
    def __init__(self,host='127.0.0.1',port=3306,user='root',passwd='a',db='pythonDB',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 elployee")
    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=%s,sex=%s,income=%d" % (fname,lname,age,sex,income))

结果

fname=张三,lname=山,age=17,sex=男,income=2000

包装为函数

#包装为函数
import contextlib

@contextlib.contextmanager
def mysql(host='127.0.0.1',port=3306,user='root',passwd='a',db='pythonDB',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 mysql() as cursor:
    row_count = cursor.execute("select * from elployee")
    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=%s,sex=%s,income=%d" % (fname,lname,age,sex,income))

fname=张三,lname=山,age=17,sex=男,income=2000

增删改查操作，这里采用函数方式

#插入数据
with mysql() as cursor:
    cursor.execute("INSERT INTO ELPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME) VALUES('MAC','Mohan',20,'M',2000)")
   
    print("添加成功")

       

#用占位符插入数据 采用格式化字符串
pymysql.paramstyle='format'
with mysql() as cursor:
    sql='''insert into elployee(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
    VALUES ('%s','%s','%s','%s','%s')
    '''
    try:
        cursor.execute(sql % ('JJB','JJ','20','M','2000'))
    except Exception as e:
        print(e)

 

 

#添加事物控制
conn = pymysql.connect('localhost',user = "root",passwd = "a",db = "pythonDB")
#获取游标
cursor=conn.cursor()
sql='''insert into elployee(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)
    VALUES ('%s','%s','%s','%s','%s')
    '''
try:
    cursor.execute(sql % ('JJB','JJ','20','M','2000'))

    conn.commit()
    print('添加成功')
except:
    conn.rollback()
    print('添加失败')
    
conn.close()   

更新操作

# 更新操作
#添加事物控制
conn = pymysql.connect('localhost',user = "root",passwd = "a",db = "pythonDB")
#获取游标
cursor=conn.cursor()
sql='''update elployee set AGE = AGE+1 where SEX ='%c' ''' %('M')
try:
    cursor.execute(sql)
    conn.commit()
    print('更新成功')
except Exception as e:
    conn.rollback()
    print(e)
conn.close()

删除操作

#删除成功
conn = pymysql.connect('localhost',user = "root",passwd = "a",db = "pythonDB")
#获取游标
cursor=conn.cursor()
sql='''delete from elployee where age<%s '''
try:
    cursor.execute(sql,'18')
    conn.commit()
    print('删除成功')
except Exception as e:
    conn.rollback()
    print(e)
conn.close()