2

I have this code that connects to a database using SQLAlchemy. I only had one schema (dbo), so this worked perfectly fine. Now I have to schemas. I've been trying to adapt this piece of code to identify the schema on the connection, but I haven't been capable of making it work. Can anyone help?


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)

0

1 Answer 1

1

You can try by changing your execute code

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

OR

   engine.execute('INSERT INTO [schemaName].tableX (log_date, log_type, message, obj, total_time) VALUES (?, ?, ?, ?, ?)', listLog)
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.