How do I use SQL parameters with python?

Question

I am using python 2.7 and pymssql 1.9.908.

In .net to query the database I would do something like this:

using (SqlCommand com = new SqlCommand("select * from Customer where CustomerId = @CustomerId", connection))
{
    com.Parameters.AddWithValue("@CustomerID", CustomerID);
    //Do something with the command
}

I am trying to figure out what the equivalent is for python and more particularly pymssql. I realize that I could just do string formatting, however that doesn't seem handle escaping properly like a parameter does (I could be wrong on that).

How do I do this in python?

Excellent question and answers. Does anyone know how to use NAMED parameters like Jason has above. So far I've only be able to use %s, %d type of stuff (positional). — Michael Kennedy
– Michael Kennedy, Commented Dec 5, 2013 at 18:49

Klesun · Accepted Answer · 2023-09-27 08:21:31Z

25

After creating a connection object db run db.execute(query, params):

cursor = db.execute('SELECT * FROM Customer WHERE CustomerID = %s', [customer_id])

then use any of the fetch... methods of the resulting cursor object.

Don't be fooled by the %s part: this is NOT string formatting, it's parameter substitution (different DB API modules use different syntax for parameter substitution -- pymssql just happens to use the unfortunate %s!-).

edited Sep 27, 2023 at 8:21

Klesun

14.1k7 gold badges66 silver badges60 bronze badges

answered Aug 5, 2010 at 0:03

Alex Martelli

887k175 gold badges1.3k silver badges1.4k bronze badges

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

4 Comments

Jason Webb Over a year ago

Thanks. The %s syntax was throwing me off.

Alex Martelli Over a year ago

@Jason, you're welcome -- and the possible confusion is exactly why I call that syntax for DB API parameters "unfortunate" (though many popular DB API modules use it, alas).

Passiday Over a year ago

Damn this misleading %s syntax! I just spent an hour trying to understand why my code complained for attempt to use %d for specifying an int parameter. I was led to think that it's the Python formatting that's in use here.

Perkins Over a year ago

Note that the latest pymssql on python3 doesn't like [customer_id], it wants a tuple (customer_id,)

Collectives™ on Stack Overflow

How do I use SQL parameters with python?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related