本文主要是介绍Python-操作MySQL,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
1. 下载pymysql
pip install pymysql
2. 导入库
import pymysql
3. 方法封装
(1) 连接数据库
def getConnect(HOST, USER, DB_PASSWD, DB_NAME):
try:
connect = pymysql.connect(host=HOST, user=USER, password=DB_PASSWD, database=DB_NAME)
print('连接成功!')
return connect
except pymysql.Error as e:
print("连接失败:" + str(e))
(2) 创建一个表
def createTable(cur, sql1, sql2):
try:
cur.execute(sql1
cur.execute(sql2)
print('表创建成功!')
except pymysql.Error as e:
print("表创建失败:" + str(e))
(3) 插入数据
def insertData(cur, connect, sql, value):
try:
cur.execute(sql, value)
connect.commit()
print('数据插入成功!')
except pymysql.Error as e:
print("数据插入失败:" + str(e))
connect.rollback()
(4) 修改数据
def updateData(cur, connect, sql, value):
try:
cur.execute(sql, value)
connect.commit()
print('数据更新成功!')
except pymysql.Error as e:
print("数据更新失败:" + str(e))
connect.rollback()
(5) 删除表中的数据
def deleteData(cur, connect, sql, value):
try:
cur.execute(sql, value)
connect.commit()
print('数据删除成功!')
except pymysql.Error as e:
print("数据删除失败:"+ str(e))
connect.rollback()
(6)查询数据
def queryData(cur, sql):
try:
cur.execute(sql)
results = cur.fetchall()
for row in results:
name = row[0]
num = row[1]
sex = row[2]
print('Name:%s \n num:%s \n SEX:%s' % (name, num, sex))
except pymysql.Error as e:
print("数据查询失败:" + str(e))
(7) 删除一张表
def deleteTable(cur, sql):
try:
cur.execute(sql)
print('表删除成功!')
except pymysql.Error as e:
print("表删除失败:" + str(e))
4. 使用举例
import pymysql
# 连接数据库
def getConnect(HOST, USER, DB_PASSWD, DB_NAME):
try:
connect = pymysql.connect(host=HOST, user=USER, password=DB_PASSWD, database=DB_NAME)
print('连接成功!')
return connect
except pymysql.Error as e:
print("连接失败:" + str(e))
# 创建一个表
def createTable(cur, sql1, sql2):
try:
cur.execute(sql1)
cur.execute(sql2)
print('表创建成功!')
except pymysql.Error as e:
print("表创建失败:" + str(e))
# 插入数据
def insertData(cur, connect, sql, value):
try:
cur.execute(sql, value)
connect.commit()
print('数据插入成功!')
except pymysql.Error as e:
print("数据插入失败:" + str(e))
connect.rollback()
# 删除表中的数据
def deleteData(cur, connect, sql, value):
try:
cur.execute(sql, value)
connect.commit()
print('数据删除成功!')
except pymysql.Error as e:
print("数据删除失败:"+ str(e))
connect.rollback()
# 更新表中的数据
def updateData(cur, connect, sql, value):
try:
cur.execute(sql, value)
connect.commit()
print('数据更新成功!')
except pymysql.Error as e:
print("数据更新失败:" + str(e))
connect.rollback()
# 查询表中的数据【仅针对我举例的'Stu'表】
def queryData(cur, sql):
try:
cur.execute(sql)
results = cur.fetchall()
for row in results:
name = row[0]
num = row[1]
sex = row[2]
print('Name:%s num:%s SEX:%s' % (name, num, sex))
except pymysql.Error as e:
print("数据查询失败:" + str(e))
# 删除一张表
def deleteTable(cur, sql):
try:
cur.execute(sql)
print('表删除成功!')
except pymysql.Error as e:
print("表删除失败:" + str(e))
def main():
connect = getConnect('localhost', 'root', 'root', 'pdsu')
cur = connect.cursor()
createTable(cur, 'DROP TABLE IF EXISTS Stu', 'CREATE TABLE Stu(Name VARCHAR(20), Num VARCHAR(20), SEX VARCHAR(20))')
insertData(cur, connect, 'INSERT INTO Stu(Name, Num, Sex) VALUES(%s, %s, %s)', ('张三', '001', '男'))
updateData(cur, connect, 'UPDATE Stu SET NUM=%s WHERE NAME=%s', ('999', '张三'))
queryData(cur, 'SELECT * FROM Stu')
deleteData(cur, connect, 'DELETE FROM Stu WHERE NAME=%s', ('张三'))
deleteTable(cur, 'DROP TABLE IF EXISTS Stu')
if __name__ == '__main__':
main()
# 输出结果:
'''
连接成功!
表创建成功!
数据插入成功!
数据更新成功!
Name:张三 num:999 SEX:男
数据删除成功!
表删除成功!
'''
这篇关于Python-操作MySQL的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!