如何识别模式上的数据库连接字符串使用SQLAlchemy，在Python?

Question

我有这个使用SQLAlchemy连接到数据库的代码。我只有一个模式(dbo)，所以它工作得非常好。现在我有两个模式。我一直在尝试调整这段代码以识别连接上的模式，但我一直无法使其工作。有人能帮忙吗?

from datetime import datetime
from sqlalchemy import create_engine
import urllib
import time
import logging

server = "xxx"
username = "xxx"
password = "xxx"
database = "db_name"
driver = "{ODBC Driver 17 for SQL Server}"

#to measure total elapsed time of the script
start_time = round(time.time(),2)
#datetime manipulation to send to table
run_time_start = datetime.now().isoformat(" ", "seconds")
#Logging Information Configuration
logging.basicConfig(filename='ControlX.log', level=logging.DEBUG,
format='%(asctime)s++%(levelname)s++%(message)s')
#to measure db connection time
start_time_db = round(time.time(),2)

#Connection object for connection to sql database to send files
print( "Connecting to database..." )
params = urllib.parse.quote_plus(
'Driver=%s;' % driver +
'Server=tcp:%s,1433;' % server +
'Database=%s;' % database +
'Uid=%s;' % username +
'Pwd={%s};' % password +
'Encrypt=yes;' +
'TrustServerCertificate=no;' +
'Connection Timeout=60;')
conn_str = 'mssql+pyodbc:///?odbc_connect=' + params
engine = create_engine(conn_str,echo=False, fast_executemany=True)
logging.info("Database Connection++Database++{}".format(round(time.time()-start_time_db, 2)))
print("Database connection succeeded")
listLog=[]
#log information treatment
with open("ControlX.log",'r') as data_file:
for line in data_file:
line_list = line.rstrip('n').split("++")
line_list[0] = line_list[0][:-4]
listLog.append(line_list)

for item in listLog:
item[4] = float(item[4])
#insert logging information about DATABASE CONNECTION ELAPSED TIME in database
engine.execute('INSERT INTO tableX (log_date, log_type, message, obj, total_time) VALUES (?, ?, ?, ?, ?)', listLog)

Answer 1

您可以通过更改执行代码来尝试

engine.execute('INSERT INTO [database].[schemaName].tableX (log_date, log_type, message, obj, total_time) VALUES (?, ?, ?, ?, ?)', listLog)

或

engine.execute('INSERT INTO [schemaName].tableX (log_date, log_type, message, obj, total_time) VALUES (?, ?, ?, ?, ?)', listLog)