话不多说 nice!!
# -*- coding:utf-8 -*-
import time
import pyodbc
import threading
import queue
'''
链接数据库的库有 pymssql pyodb ctds Turboodbc
pyodb ---- VVVV
{SQL Server} - released with SQL Server 2000
{SQL Native Client} - released with SQL Server 2005 (also known as version 9.0)
{SQL Server Native Client 10.0} - released with SQL Server 2008
{SQL Server Native Client 11.0} - released with SQL Server 2012
{ODBC Driver 11 for SQL Server} - supports SQL Server 2005 through 2014
{ODBC Driver 13 for SQL Server} - supports SQL Server 2005 through 2016
{ODBC Driver 13.1 for SQL Server} - supports SQL Server 2008 through 2016
{ODBC Driver 17 for SQL Server} - supports SQL Server 2008 through 2017
运行“ odbcad32.exe”进入此“ ODBC管理器”窗口 查看驱动
'''
class Write_SQL():
def __init__(self):
starttime = time.time()
self.q = queue.Queue()
super().__init__()
def Connection_sql(self,sqlservernum,localip,database,user,password):
'''数据库版本 int,localip 本地ip 连接的数据库 用户名 密码'''
if 5 <= int(sqlservernum) <= 14:
# driver = '{SQL Server Native Client 11.0}'
driver = '{ODBC Driver 11 for SQL Server}'
elif 14 < int(sqlservernum) <= 16:
driver = '{ODBC Driver 13 for SQL Server}'
print(driver,localip,database,user,password)
# autocommit=True 自动提交
# 自动更新数据库连接
sql = threading.Thread(target=self.update_sql,args=(driver,localip,database,user,password))
# 守护进程 主进程结束 子进程也结束 防止陷入僵尸进程
sql.setDaemon(True)
sql.start()
self.conn = self.q.get()
# 动态更新数据库连接 一个小时更新一次
def update_sql(self,driver,localip,database,user,password):
while True:
try:
self.conn = pyodbc.connect('DRIVER={};SERVER={};DATABASE={};UID={};PWD={}'.format(driver,localip,database,user,password))
except Exception as EX:
time.sleep(5)
print(repr(EX))
else:
self.q.put(self.conn)
time.sleep(60*60)
self.conn.close()
def Execute_statement(self,sqlcode):
try:
self.cursor = self.conn.cursor()
self.cursor.execute(sqlcode)
except pyodbc.IntegrityError as PITE:
raise pyodbc.IntegrityError('数据写入库时 --->>> 主键出现重复 sql == > "{}"'.format(sqlcode))
except pyodbc.ProgrammingError as PPME:
raise pyodbc.ProgrammingError('语法错误:sql == > "{}"'.format(sqlcode))
else:
self.conn.commit()
def Read_data(self,sqlcode):
try:
self.cursor = self.conn.cursor()
self.cursor.execute(sqlcode)
except Exception as EX:
print(repr(EX))
else:
return self.cursor.fetchall()
def __del__(self):
sys.exit(0)
os._exit()
if __name__ == '__main__':
WS = Write_SQL()
WS.Connection_sql(14,'192.168.1.5','GJC_Key','sa','zxc1230.')
sql = "select top 100000 Keywords from ConnectKeywords_ali where Keywords like '{}%' order by UpdateTime;".format('a')
datalist = WS.Read_data(sql)
print(datalist,'----')