python操作数据库的过程：
创建连接—获取游标—执行命令—关闭游标—关闭连接
; python访问mysql 要用pymysql库
pymysql需要安装：pip install pymysql
安装完之后导入pymysql， 创建连接
连接数据库的参数按照实际情况添加，端口号默认是3306，我的有冲突，改成了3307
import pymysql
def mysql_db():
conn = pymysql.connect(
host="127.0.0.1",
port=3307,
database="lebo",
charset="utf8",
user="root",
passwd="root"
)
if __name__ == '__main__':
mysql_db()
关于游标，执行SQL，查询数据
游标需要创建游标还要关闭游标，为了防止忘关游标，所以用with
try:
with conn.cursor() as cursor:
sql = "select * from user"
cursor.execute(sql)
datas = cursor.fetchall()
print("获取的数据：\n", datas)
except Exception as e:
print("数据库操作异常：\n", e)
finally:
conn.close()
cursor中获取数据的fetchall，fetchone，fetchmany
fetchall：获取当前SQL语句能查出来的全部数据
fetchone：每次获取一条数据。但是获取到这条数据后，指针会往后移一行数据
fetchmany：直接告诉它想要多少条数据
插入一条数据
重点一个是commit，一个是rollback。有提交的地方一定要有回滚。回滚到上次提交的地方
try:
with conn.cursor() as cursor:
value = "12,'鲁肃'"
sql = f"insert into user values ({value});"
cursor.execute(sql)
conn.commit()
print("提交成功")
except Exception as e:
conn.rollback()
print("数据库操作异常：\n", e)
finally:
conn.close()
添加多条数据
try:
with conn.cursor() as cursor:
value = "(13,'张三'),(14,'李四'),(15,'王五')"
sql = f"insert into user values {value};"
cursor.execute(sql)
conn.commit()
print("提交成功")
except Exception as e:
conn.rollback()
print("数据库操作异常：\n", e)
finally:
conn.close()
表中可以看到已经插入成功
修改、更新数据
只需要修改添加的SQL语句，其他不变
增删改查，除了查询，其他都需要提交
try:
with conn.cursor() as cursor:
sql = f"update user set name='封控了' where id = 1 ;"
cursor.execute(sql)
conn.commit()
print("提交成功")
except Exception as e:
conn.rollback()
print("数据库操作异常：\n", e)
finally:
conn.close()
删除数据
try:
with conn.cursor() as cursor:
sql = "delete from user where id = 12;"
cursor.execute(sql)
conn.commit()
print("删除成功")
except Exception as e:
conn.rollback()
print("数据库操作异常：\n", e)
finally:
conn.close()
封装一个需要自己写SQL语句的mysql工具类
'''
封装一个mysql工具类(需要自己写SQL语句)
功能：mysql数据库操作
步骤：
1.连接数据库
2.通过连接对象，获取游标对象
3.增删改查操作
方法：
1.查
2.增删改 commit,rollback
'''
import pymysql
config = {
"host": "127.0.0.1",
"port": 3307,
"database": "lebo",
"charset": "utf8",
"user": "root",
"passwd": "root"
}
class Mysqldb():
def __init__(self):
self.conn = self.get_conn()
self.cursor = self.get_cursor()
def get_conn(self):
conn = pymysql.connect(**config)
return conn
def get_cursor(self):
cursor = self.conn.cursor()
return cursor
def select_all(self, sql):
self.cursor.execute(sql)
return self.cursor.fetchall()
def select_one(self, sql):
self.cursor.execute(sql)
return self.cursor.fetchone()
def select_many(self, sql, num):
self.cursor.execute(sql)
return self.cursor.fetchmany(num)
def commit_data(self, sql):
try:
self.cursor.execute(sql)
self.conn.commit()
print("提交成功")
except Exception as e:
print("提交出错\n:", e)
self.conn.rollback()
def __del__(self):
self.cursor.close()
self.conn.close()
调用
from mysql_util import Mysqldb
my_db = Mysqldb()
sql = "select * from user where id>5"
select_all = my_db.select_all(sql)
print("查询所有数据：\n", select_all)
select_one = my_db.select_one(sql)
print("查询一条数据：\n", select_one)
select_many = my_db.select_many(sql, 3)
print("查询3条数据：\n", select_many)
value = (16, 'BBQ')
sql = f"insert into user values {value}"
insert_one = my_db.commit_data(sql)
values = "(17, 'aaa'), (18, 'bbb'), (19, 'ccc')"
sql = f"insert into user values {values}"
insert_many = my_db.commit_data(sql)
sql = "update user set name = '出不去了' where id = 17"
my_db.commit_data(sql)
sql = "delete from user where id = 17"
my_db.commit_data(sql)
封装一个不用写SQL语句，只需要填参数的工具类
只写了基本的增删改查，后面可以根据实际情况添加
import pymysql
class Database():
def __init__(self, **config):
try:
self.__conn = pymysql.connect(**config)
self.__cursor = self.__conn.cursor()
except Exception as e:
print("数据库连接失败：\n", e)
def select_one(self, table_name, factor_str='', field="*"):
if factor_str == '':
sql = f"select {field} from {table_name}"
else:
sql = f"select {field} from {table_name} where {factor_str}"
self.__cursor.execute(sql)
return self.__cursor.fetchone()
def select_many(self, num, table_name, factor_str='', field="*"):
if factor_str == '':
sql = f"select {field} from {table_name}"
else:
sql = f"select {field} from {table_name} where {factor_str}"
self.__cursor.execute(sql)
return self.__cursor.fetchmany(num)
def select_all(self, table_name, factor_str='', field="*"):
if factor_str == '':
sql = f"select {field} from {table_name}"
else:
sql = f"select {field} from {table_name} where {factor_str}"
self.__cursor.execute(sql)
return self.__cursor.fetchall()
def insert(self,table_name, value):
sql = f"insert into {table_name} values {value}"
try:
self.__cursor.execute(sql)
self.__conn.commit()
print("插入成功")
except Exception as e:
print("插入失败\n", e)
self.__conn.rollback()
def update(self, table_name, val_obl,change_str):
sql = f"update {table_name} set"
for key, val in val_obl.items():
sql += f" {key} = {val},"
sql = sql[:-1]+" where "+change_str
try:
self.__cursor.execute(sql)
self.__conn.commit()
print("修改成功")
except Exception as e:
print("修改失败\n", e)
self.__conn.rollback()
def delete(self,table_name, item):
sql = f"delete from {table_name} where {item}"
try:
self.__cursor.execute(sql)
self.__conn.commit()
print("删除成功")
except Exception as e:
print("删除失败\n", e)
self.__conn.rollback()
引用 ↓
from mysql_normal_util import Database
config = {
"host": "127.0.0.1",
"port": 3307,
"database": "lebo",
"charset": "utf8",
"user": "root",
"passwd": "root"
}
db = Database(**config)
select_one = db.select_one("user")
print(select_one)
select_many = db.select_many(3, "user")
print(select_many)
select_all = db.select_all("user", "id>10")
print(select_all)
db.insert("user","(20,'111')")
db.insert("user", "(21,'123'),(22,'456')")
db.update("user", {"name": "222"}, "id=20")
db.update("user", {"id": "23", "name": "12345"}, "id=103")
db.delete("user", "id=23")
Original: https://blog.csdn.net/aijaijgnaw/article/details/124729427
Author: 是佳佳呀~
Title: python操作mysql数据库
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/668446/
转载文章受原作者版权保护。转载请注明原作者出处！