python操作mysql
读取yaml文件的类,之后会用到
"""
@File : Read_YAML.py
@Contact : xxx.com
@Modify Time @Author @Version
------------ ------- --------
2021/12/16 6:19 下午 xxx 1.0
@Desciption:
"""
import yaml
import os
class Read_YAML:
def __init__(self, file_path=None):
if file_path is None:
self.file_path = os.path.join(os.path.join(os.path.dirname(os.path.dirname(__file__)), "config"),
"deviceconfig.yaml")
else:
self.file_path = file_path
self.data = self.read()
def read(self):
with open(self.file_path, 'r', encoding="utf-8") as f:
content = yaml.load(f, Loader=yaml.FullLoader)
return content
def get_value(self, dataOne, dataTwo):
return self.data[dataOne][dataTwo]
if __name__ == "__main__":
content = Read_YAML().read()
print(content)
value = Read_YAML().get_value("device_info_0", "deviceName")
print(value)
yaml配置文件的内容
test04:
host: xx.xx.xx.xx
port: 3306
user: test_dev04
passwd: xxxxxxxx
db: czb_coupon
test:
host: xx.xx.xx.xx
port: 3306
user: test_dev06
passwd: xxxxx
db: czb_coupon
通过pymysql
"""
@File : Operation_Mysql.py
@Modify Time @Author @Version
------------ ------- --------
@Desciption:
"""
import os.path
from One.Read_YAML import *
import pymysql
class DB:
def __init__(self, environment):
# 定义一个环境,比如说有多套测试环境,通过test01,test02来区别
self.connect = self.connect_DB(environment)
# 在初始化函数的同时就调用获取链接的connect_DB方法,并将链接返回
self.cur = self.connect.cursor()
# 通过数据库链接,获取游标
def connect_DB(self, environment):
config_path = os.path.join(os.path.join(os.path.dirname(os.path.dirname(__file__)), "config"), "db_config.yaml")
# 获取文件位置
read_yaml = Read_YAML(config_path)
# 读取配置文件
host = read_yaml.get_value(environment, "host")
port = read_yaml.get_value(environment, "port")
user = read_yaml.get_value(environment, "user")
passwd = read_yaml.get_value(environment, "passwd")
# 获取数据库的信息
db = read_yaml.get_value(environment, "db")
# db可以注释掉,如果你在每个sql的每张表前都写好了是哪个库,这个就没用,但是要记得同时将下面一行的db也删除掉,如果你的操作全在一个库里,你就写死,否则就注释掉
connect = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db)
# 链接数据库
return connect
# 返回数据库链接,供初始化函数使用
def __del__(self):
self.connect.close()
self.cur.close()
# 析构函数,实例删除时触发
def query(self, sql):
self.cur.execute(sql)
# 执行查询sql
return self.cur.fetchall()
# 返回执行查询sql的信息
def exec(self,sql):
# 除了查询之外,还有新增删除和修改,这三个操作就会有失败的情况,那这个时候我们就需要捕捉报错
try:
self.cur.execute(sql)
self.connect.commit()
# 提交sql执行结果
except Exception as e:
self.connect.rollback()
# 如果报错的话就回滚
print(str(e))
if __name__ == "__main__":
sql = "select amount_pay,return_payment,amount_balance,refund_dt from settlement_order.order_gross_profit where order_code = 'QF0001164822112171IIL01';"
environment = "test04"
db = DB(environment)
result = db.query(sql)
print(type(result))
print(result)
print(result[0][0])
结果
<class 'tuple'>
((Decimal('0.00'), Decimal('189.42'), Decimal('189.42'), None),)
0.00
兼容MySQLdb
MySQLdb只能适用于python2.x,而pymysql是MySQLdb在python3.x的替代品,如果要在python3.x上使用MySQLdb的话,需要在包的__init__.py文件或者说你使用MySQLdb的文件中这个亚子
import pymysql
pymysql.install_as_MySQLdb()