PyMySQL模块
Python—连接mysql数据库代码。文章来源：https://www.toymoban.com/news/detail-564924.html
- 原理图
from pymysql import connect, Error
class Mysql:
"""初始化Mysql数据库"""
def __init__(self, hostname, port, username, password):
self.hostname = hostname
self.port = port
self.username = username
self.password = password
"""创建数据库连接对象"""
def conn_db(self):
try:
conn = connect(host=self.hostname, port=self.port, user=self.username, password=self.password)
return conn
except Error as e:
print(e)
"""执行SQL语句"""
def exec_sql(self, sql):
connection = None
try:
connection = self.conn_db()
with connection.cursor() as cursor:
cursor.execute(create_database) # 没有数据库，第一次需要创建。
cursor.execute(choice_database) # 选择数据库
cursor.execute(sql) # 执行SQL语句
if 'CREATE' in sql or 'INSERT' in sql or 'UPDATE' in sql or 'DELETE' in sql or 'DROP' in sql:
connection.commit()
print("数据操作成功！")
elif 'SHOW' in sql or 'SELECT' in sql:
result = cursor.fetchall()
print(f"result:{result}")
print("数据库读取成功!")
except Error as e:
print(e)
finally:
# 关闭游标
cursor.close()
# 关闭连接
connection.close()
if __name__ == '__main__':
open_sql = Mysql(hostname='127.0.0.1', port=3306, username='root', password='123456')
open_sql.exec_sql(update_users) # 单个SQL语句的执行
sqls = [create_users_table, insert_users, update_users, delete_user, drop_table, drop_database] # 多个SQL语句执行
for sql in sqls:
open_sql.exec_sql(sql)
标准化数据库连接格式（后续会持续更新）文章来源地址https://www.toymoban.com/news/detail-564924.html
# 创建数据库
create_database = """
CREATE DATABASE IF NOT EXISTS students
"""
# 选择数据库
choice_database = """
use students
"""
# 建表
create_users_table = """
CREATE TABLE IF NOT EXISTS users(
id INT AUTO_INCREMENT,
name TEXT NOT NULL,
age INT,
gender TEXT,
PRIMARY KEY (id)
)
"""
# 插入记录，注意：`users`, `name`, `age`, `gender`，不是引号包裹，是键盘左上角的撇号
insert_users = """
INSERT INTO
`users` (`name`, `age`, `gender`)
VALUES
('zhangsan',23,'male'),
('lisi',24,'female'),
('wangwu',25,'male');
"""
# 更改
update_users = """
UPDATE `users` set `name`='jack' where `name` = 'zhangsan'
"""
# 查询
select_users = "SELECT * FROM users"
# 删除记录
delete_user = """
DELETE from users where `name`="lisi"
"""
# 删除表
drop_table = "DROP table users"
# 删除数据库
drop_database = "DROP database students"
到了这里，关于Python模块—PyMySQL模块的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
