Execute a SQL Stored Procedure within a Python Script

Question

I'm trying to execute a SQL stored proc within a Python script and having issues with the syntax. I have tried a combination of various examples I have found but none seem to work. Here's what I have tried:

county = 100200
confirmed = 123456
death = 12
labTestCount = 0    
#Example1
conn.execute('{CALL [spInsertCOVIDData](@County,@Confirmed,@Death,@LabTestCount)}', ('county', 'confirmed', 'death', 'labTestCount'))

#Example2
query = "EXEC [spInsertCOVIDData] (@County,@Confirmed,@Death,@LabTestCount)", (county, confirmed, death, labTestCount)
conn.execute(query)

#Example3
query = "EXEC [spInsertCOVIDData] @County=?, @Confirmed=?, @Death=?, @LabTestCount=?",(county, confirmed, death, labTestCount)
conn.execute(query)

#Example4
query = "EXEC [spInsertCOVIDData] @County='county', @Confirmed='confirmed', @Death='death', @LabTestCount='labTestCount'"
conn.execute(query)

I get a mixture of various error from:

('The SQL contains 0 parameter markers, but 4 parameters were supplied', 'HY000')

or

The first argument to execute must be a string or unicode query.

Assuming you are using psycopg2 I am used to have the arguments in the sql string to be %s not @County, so conn.execute('{CALL [spInsertCOVIDData](%s ,%s, %s, %s)}', (val1, val2, val3, val4)) — Bruno Vermeulen
– Bruno Vermeulen, Commented Sep 26, 2020 at 2:43

Zhorov · Accepted Answer · 2020-09-26 06:32:17Z

1

You may try to use parameters in your statement using ? as a placeholder and pass the actual values of each parameter:

county = 100200
confirmed = 123456
death = 12
labTestCount = 0

query = "EXEC [spInsertCOVIDData] @County=?, @Confirmed=?, @Death=?, @LabTestCount=?"
conn.execute(query, (county, confirmed, death, labTestCount))

answered Sep 26, 2020 at 6:32

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

Collectives™ on Stack Overflow

Execute a SQL Stored Procedure within a Python Script

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related