#!/usr/bin/python3
import pymysql
import random
# 数据库增加表
def add_table(mysql):
# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="", database="testdb")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 定义要执行的SQL语句
# 执行SQL语句
cursor.execute(mysql)
# 关闭光标对象
cursor.close()
# 关闭数据库连接
db.close()
# sql = """CREATE TABLE tables1 (
# id INT auto_increment PRIMARY KEY ,
# name CHAR(10) NOT NULL UNIQUE,
# age TINYINT NOT NULL
# )ENGINE=innodb DEFAULT CHARSET=utf8
# """
# add_table(sql)
# 向数据库表增加数据
def add_field(mysql, insert_data):
# 连接database
db = pymysql.connect(host="localhost", user="root", password="", database="testdb")
# 得到一个可以执行SQL语句的光标对象
cursor = db.cursor()
try:
# 批量执行多条插入SQL语句
cursor.executemany(mysql, insert_data)
# 提交事务
db.commit()
except Exception as e:
# 有异常,回滚事务
db.rollback()
cursor.close()
db.close()
#
# sql = "INSERT INTO USER(name, age) VALUES (%s, %s);"
# data = [("Alex", 36), ("Egon", 20), ("Yuan", 21), ('詹姆斯', '35')]
# add_field(sql, data)
# 删除
def delete_data(mysql, value):
# 连接database
db = pymysql.connect(host="localhost", user="root", password="", database="testdb")
# 得到一个可以执行SQL语句的光标对象
cursor = db.cursor()
try:
cursor.execute(mysql, value)
# 提交事务
db.commit()
except Exception as e:
# 有异常,回滚事务
db.rollback()
cursor.close()
db.close()
# sql = "DELETE FROM USER1 WHERE name=%s;"
# delete_data(sql, 'sssss')
# 修改
def update_data(mysql, value):
# 导入pymysql模块
import pymysql
# 连接database
db = pymysql.connect(host="localhost", user="root", password="", database="testdb")
# 得到一个可以执行SQL语句的光标对象
cursor = db.cursor()
try:
# 执行SQL语句
cursor.execute(mysql, value)
# 提交事务
db.commit()
except Exception as e:
# 有异常,回滚事务
db.rollback()
cursor.close()
db.close()
# # 修改数据的SQL语句
# sql = "UPDATE USER SET age=%s WHERE id=%s;"
#
# update_data(sql, [20, 1])
# 查找
def select_data(mysql):
# 打开数据库连接
db = pymysql.connect(host="localhost", user="root", password="", database="testdb")
# 使用cursor()方法获取操作游标
cur = db.cursor()
# 1.查询操作
# 编写sql 查询语句 user 对应我的表名
# sql = "select * from user"
try:
cur.execute(mysql) # 执行sql语句
if 'where' not in mysql:
results = cur.fetchall() # 获取查询的所有记录
print("id", "name")
# 遍历结果
for row in results:
id = row[0]
name = row[1]
print(id, name)
else:
cur = cur.fetchone()
print(cur)
except Exception as e:
raise e
finally:
db.close() # 关闭连接
sql = "select name, age from user where name='詹姆斯';"
select_data(sql)
参考https://www.cnblogs.com/chongdongxiaoyu/p/8951433.html
改进:
# _*_coding:utf-8_*_
import os
import pymysql
import random
import yaml
# 读取yaml数据库配置文件
def read_yaml(key_type, yaml_name="sql_yaml.yaml"):
# 获得当前文件的路径
cu_path = os.path.dirname(os.path.realpath(__file__))
# 获取yaml文件路径
yaml_path = os.path.join(cu_path, yaml_name)
# open方法打开直接读出来
file = open(yaml_path, 'r', encoding='utf-8')
str_data = file.read() # 读出来是字符串
data_dict = yaml.load(str_data) # 用load方法转字典
return data_dict[key_type]
# 执行sql命令
def exec_sql(sql_type, mysql):
# 打开数据库连接
db = pymysql.connect(
host=read_yaml('host'),
user=read_yaml("user"),
password=read_yaml("password"),
database=read_yaml("database")
)
"""
执行sql语句
:param sql_type:
:param sql:
:return:
"""
# 使用cursor()方法获取操作游标
global result
cursor = db.cursor()
try:
if sql_type == 'select_one':
cursor.execute(mysql)
result = cursor.fetchone()
elif sql_type == 'select_list':
cursor.execute(mysql)
result = cursor.fetchall()
# for row in result:
# id = row[0]
# name = row[1]
# # password = row[2]
# print(id, name)
elif sql_type == 'update' or sql_type == 'del' or sql_type == 'insert':
result = cursor.execute(mysql)
db.commit()
cursor.close()
db.close()
return result
except Exception as e:
print("执行sql语句报错:\n{0}".format(e))
if __name__ == '__main__':
sql = "select name, age from user;"
res = exec_sql('select_list', sql)
print(res)
# r = read_yaml('host')
# print(r)
yaml文件配置:
host:
“”
user:
“”
password:
“”
database:
“” # 被连接的库的名字