如何使用python中的sqlalchemy在数据库连接字符串上识别模式？

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)