Python语言经过了很多年的发展,生态非常丰富,热度只增不减,今天通过具体的案例与代码介绍如何使用Python来链接MySQL,并实现对MySQL的增删改查。
Python3: 本文演示版本为 Python 3.8.3rc1
MySQL5: 本文演示版本为 5.6.50
Python库: pymysql,mysql.connector
系统环境: Window10
命令如下:
pip3 install mysql-connector
pip3 install mysql-connector-python
pip3 install pymysql
SQL如下:
CREATE TABLE `student` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,`gender` varchar(10) DEFAULT NULL,`number` int(10) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
代码如下:
class DB:cursor = conn = Nonedef __init__(self):self.initDB()#初始化数据库对象def initDB(self):try:self.conn = mysql.connector.connect(host = 'localhost', # 数据库地址,请根据需要修改user = 'root', # 数据库用户名port = 3306, # 数据库端口password = 'root', # 数据库密码database = 'test', # 数据库auth_plugin ='mysql_native_password')self.cursor = self.conn.cursor()except Exception as e:print(e)# 如果为查询指令用此方法def query(self, sql, useDict = False):sql = sql.encode('utf-8')try:self.cursor.execute(sql)result = self.cursor.fetchall()if(useDict == True):fields = self.cursor.descriptionresult = self.getDictData(fields, result)return resultexcept Exception as e:print(e)# 如果为增删改用此方法def execute(self, sql):sql = sql.encode('utf-8')try:self.cursor.execute(sql)self.conn.commit()except Exception as e:# 如果失败则回滚self.conn.rollback()print(e)# 关闭数据库def close(self):self.conn.close()"""@description: 格式化数组,返回字典key格式@param: @Returns: """def getDictData(self, fields, result):field = []for i in fields:field.append(i[0])res = []for iter in result:line = {}for index in range(0, len(field)):line[field[index]] = iter[index]res.append(line)return res
db = DB()
# 增加数据
db.execute("insert into student(name,gender,number) value ('小红', '女', '12345'), ('小张', '男', '45678')")
看到数据库成功添加进去了数据。
db.execute("delete from student where name='小张'")
进入数据库看到小张被删除。
db.execute("update student set number = '67890' where name='小红'")
进入数据库看到小红学号被更新为67890。
students = db.query("select * from student", True)
print(students)
通过控制台可以看到打印出最新的小红的信息。
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from pymysql import connect
import mysql.connectorclass DB:cursor = conn = Nonedef __init__(self):self.initDB()#初始化数据库对象def initDB(self):try:self.conn = mysql.connector.connect(host = 'localhost', # 数据库地址,请根据需要修改user = 'root', # 数据库用户名port = 3306, # 数据库端口password = 'root', # 数据库密码database = 'test', # 数据库auth_plugin ='mysql_native_password')self.cursor = self.conn.cursor()except Exception as e:print(e)# 如果为查询指令用此方法def query(self, sql, useDict = False):sql = sql.encode('utf-8')try:self.cursor.execute(sql)result = self.cursor.fetchall()if(useDict == True):fields = self.cursor.descriptionresult = self.getDictData(fields, result)return resultexcept Exception as e:print(e)# 如果为增删改用此方法def execute(self, sql):sql = sql.encode('utf-8')try:self.cursor.execute(sql)self.conn.commit()except Exception as e:# 如果失败则回滚self.conn.rollback()print(e)# 关闭数据库def close(self):self.conn.close()"""@description: 格式化数组,返回字典key格式@param: @Returns: """def getDictData(self, fields, result):field = []for i in fields:field.append(i[0])res = []for iter in result:line = {}for index in range(0, len(field)):line[field[index]] = iter[index]res.append(line)return res# 开始数据库连接
db = DB()# 增加数据
#db.execute("insert into student(name,gender,number) value ('小红', '女', '12345'), ('小张', '男', '45678')")#删除数据
#db.execute("delete from student where name='小张'")#更新数据
#db.execute("update student set number = '67890' where name='小红'")#查询数据
students = db.query("select * from student", True)
print(students)db.close()
以上就是今天要讲的内容,本文实现了Python连接MySQL实现增删改查。喜欢的话,给个关注,更多精彩,即将呈现。