Pass database name variable in SQL query using Python

Question

My config file has database details like below:

config = configparser.ConfigParser()            
config.read("C:/configsql.ini")
_SERVER_NAME = config['SQL']['SERVER_NAME']
_DATABASE = config['SQL']['DATABASE']
_USERNAME = config['SQL']['USERNAME']
_PASSWORD = config['SQL']['PASSWORD']

How do I utilize _DATABASE variable in my SQL Queries which are executed via python. Currently, SQL queries are hardcoded with database name - TEST.

Below are two types of SQL Queries used in my python code:

connsql = self.sql_connection()
query = "select distinct top 2 filing_id, StatusIndicator from [TEST].[dbo].[FILE] where ID = ?"  
resultset = connsql.cursor().execute(query,values).fetchall()
connsql.cursor().commit()
connsql.close()

And,

query ='''
        IF EXISTS(select [file_name], [file] from [TEST].[dbo].[FILE] where ID = ? and TypeID = 1)
        BEGIN 
            select [file_name], [file] from [TEST].[dbo].[FILE] where ID = ? and TypeID = 1
        END
        ELSE
        BEGIN 
            SELECT - 1
        END                   
      '''
values = (Id,Id)
results = connsql.cursor().execute(query, values).fetchall()

Does this answer your question? How to pass database name as a parameter to sql statement in python? — sugar2code
– sugar2code, Commented Dec 13, 2019 at 22:21
The approach works for my first query format where query =" " But, how to make it work for second query format where it has 3 quotes query =''' ''' — user2961127
– user2961127, Commented Dec 13, 2019 at 22:22
The solution linked by @sugar2code should only be used where you're not accepting user input as database name - it's wide open to injection. — OwlsSleeping
– OwlsSleeping, Commented Dec 13, 2019 at 22:32

David Browne - Microsoft · Accepted Answer · 2019-12-13 22:25:25Z

2

How do I utilize _DATABASE variable in my SQL Queries which are executed via python.

You should normally specify the database in your sql_connection, so you can use 2-part names instead of 3-part names in your queries.

answered Dec 13, 2019 at 22:25

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pass database name variable in SQL query using Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related