python连接mysql增删改查_Python 连接Mysql数据库并进行增删改查操作

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

pip install pymysql

下载成功之后导入pymysql

import pymysql

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

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

print (conn)

print (type(conn))

输出 如下，则连接成功

创建数据库

#创建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

try:

cursor.execute(sql,'18')

conn.commit()

print('删除成功')

except Exception as e:

conn.rollback()

print(e)

conn.close()