可直接复用的代码
方案一
py脚本
import pymysql
import traceback
import time
class MYSQL_INTERFACE:
def __init__(self, ip, port, user, passwd, db_name):
self.db = pymysql.connect(
host=ip,
port=port,
user=user,
passwd=passwd,
db=db_name,
charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
# 读取数据
def read_db(self, cmd):
self.cursor = self.db.cursor() # 获取操作游标
try:
self.cursor.execute(cmd)
db_data = self.cursor.fetchall()
return db_data
except:
self.cursor.close() # 关闭游标
traceback.print_exc()
# 写入一条数据
def write_db(self, cmd):
self.cursor = self.db.cursor() # 获取操作游标
try:
self.cursor.execute(cmd)
self.db.commit()
except:
self.db.rollback() # 错误回滚
self.cursor.close() # 关闭游标
traceback.print_exc()
# 批量写入字典数据
def write_many_data(self, data_list, table_name):
self.cursor = self.db.cursor() # 获取操作游标
try:
sql_cmd = self.generate_sql_cmd(data_list, table_name)
self.cursor.executemany(sql_cmd, data_list)
self.db.commit()
except:
self.db.rollback() # 错误回滚
self.cursor.close() # 关闭游标
traceback.print_exc()
# 生成批量写入字典数据的命令
def generate_sql_cmd(self, data_list, table_name):
data = data_list[0]
cols = ", ".join('`{}`'.format(k) for k in data.keys())
val_cols = ', '.join('%({})s'.format(k) for k in data.keys())
sql_cmd = "replace INTO {}({}) VALUES({})".format(table_name, cols, val_cols)
return sql_cmd
# 关闭数据库链接
def close_db(self):
self.cursor.close() # 关闭游标
self.db.close() # 关闭游标
def main():
# 链接数据库,需要配置: ip, 端口号, 用户, 密码, 数据库名
db = MYSQL_INTERFACE('127.0.0.1', 3306, 'root', 'root', 'log_info')
# 读取数据库,返回批量字典数据
cmd = "select * from log_info_table"
db_data = db.read_db(cmd)
print(db_data)
# 写入一条数据(存在主键时,replace会防止重复数据的写入)
write_cmd = "replace into log_info_table(datatime, log_file_path) values(2,100)"
db.write_db(write_cmd)
# 批量写入字典数据
data_list = [
{"datatime":30, "log_file_path":2, "html_file_path":2, "owner":2},
{"datatime":40, "log_file_path":20, "html_file_path":20, "owner":20},
]
db.write_many_data(data_list, 'log_info_table')
# 关闭数据库链接
db.close_db()
if __name__ == "__main__":
try:
start_time = time.time()
main()
end_time = time.time()
print("Program running time: {:.2f}s".format(end_time - start_time))
except:
traceback.print_exc()
方案二:db信息配置和代码互相隔离
同级目录下,json配置文件和python代码
config.json文件
{
"log_db":{
"ip": "127.0.0.1",
"port": 3306,
"user": "root",
"passwd": "root",
"db_name": "log_info"
},
"log_db1":{
"ip": "127.0.0.1",
"port": 3306,
"user": "root",
"passwd": "root",
"db_name": "log_info"
}
}
sql_interface.py文件
import os
import pymysql
import traceback
import time
import json
# 读写数据库
class MYSQL_INTERFACE:
def __init__(self, ip, port, user, passwd, db_name):
self.db = pymysql.connect(
host=ip,
port=port,
user=user,
passwd=passwd,
db=db_name,
charset='utf8',
cursorclass=pymysql.cursors.DictCursor)
# 读取数据
def read_db(self, cmd):
self.cursor = self.db.cursor() # 获取操作游标
try:
self.cursor.execute(cmd)
db_data = self.cursor.fetchall()
return db_data
except:
self.cursor.close() # 关闭游标
traceback.print_exc()
# 写入一条数据
def write_db(self, cmd):
self.cursor = self.db.cursor() # 获取操作游标
try:
self.cursor.execute(cmd)
self.db.commit()
except:
self.db.rollback() # 错误回滚
self.cursor.close() # 关闭游标
traceback.print_exc()
# 批量写入字典数据
def write_many_data(self, data_list, table_name):
self.cursor = self.db.cursor() # 获取操作游标
try:
sql_cmd = self.generate_sql_cmd(data_list, table_name)
self.cursor.executemany(sql_cmd, data_list)
self.db.commit()
except:
self.db.rollback() # 错误回滚
self.cursor.close() # 关闭游标
traceback.print_exc()
# 生成批量写入字典数据的命令
def generate_sql_cmd(self, data_list, table_name):
data = data_list[0]
cols = ", ".join('`{}`'.format(k) for k in data.keys())
val_cols = ', '.join('%({})s'.format(k) for k in data.keys())
sql_cmd = "replace INTO {}({}) VALUES({})".format(table_name, cols, val_cols)
return sql_cmd
# 关闭数据库链接
def close_db(self):
self.cursor.close() # 关闭游标
self.db.close() # 关闭游标
# 从json配置文件中获取db信息;以后仅需配置json文件即可
def read_config_json():
# 当前脚本所在路径
dir_path = os.path.split(os.path.realpath(__file__))[0]
config_file = os.path.join(dir_path, "config.json")
with open(config_file, "r") as f:
config_json = json.load(f)
return config_json
"""
返回值为db对象,存在三个接口:
1、 db.read_db(cmd) # 读取数据库的命令
2、 db.write_db(write_cmd) # 写入数据库的命令
3、 # 批量写入字典数据
# 参数1:批量字典数据, 数据表的名字
db.write_many_data(data_list, 'log_info_table')
可以在其他文件引入这个函数,去操作相应的数据库
"""
# 操作某一个数据库;参数:json配置文件的键,去链接相应的数据库
def operat_database(db_key):
db_info = read_config_json()[db_key]
db = MYSQL_INTERFACE(db_info["ip"], db_info["port"], db_info["user"], db_info["passwd"], db_info["db_name"])
return db
def main():
# 参数:操作哪个数据库(json配置文件的键)
db = operat_database("log_db")
# db = operat_database("log_db1")
# 读取数据库,返回批量字典数据
cmd = "select * from log_info_table"
db_data = db.read_db(cmd)
print(db_data)
# 写入一条数据(存在主键时,replace会防止重复数据的写入)
write_cmd = "replace into log_info_table(datatime, log_file_path) values(2,100)"
db.write_db(write_cmd)
# 批量写入字典数据
data_list = [
{"datatime":30, "log_file_path":2, "html_file_path":2, "owner":2},
{"datatime":40, "log_file_path":20, "html_file_path":20, "owner":20},
]
db.write_many_data(data_list, 'log_info_table')
# 关闭数据库链接
db.close_db()
if __name__ == "__main__":
try:
start_time = time.time()
main()
end_time = time.time()
print("Program running time: {:.2f}s".format(end_time - start_time))
except:
traceback.print_exc()
在django内使用:
由于django项目的根路径是从项目开始的,所以直接
from common_interface.sql_interface import operat_database
就可以