Executing SQL with " " from Python

Question

I have trouble executing a SQL-statement from Python because of the " " in the selection. I think I've tried every combination, but can't figure it out. Can you please help me?

cnxn = pyodbc.connect("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\\python33\salesdb.mdb")
cursor = cnxn.cursor()
v_sql = "SELECT DISTINCT tblSeller.ID, tblSeller.Navn FROM tblResult INNER JOIN tblSeller ON tblResult.SellerID = tblSeller.ID WHERE (((tblSeller.Name)="Robert Smith" ))"
cursor.execute(v_sql)

The problem is the " around Robert Smith in the SQL.

Martijn Pieters · Accepted Answer · 2013-12-10 10:33:12Z

2

You would escape the quotes or use a different quoting style. Single quotes would do here, for example.

Using single quotes:

v_sql = 'SELECT DISTINCT tblSeller.ID, tblSeller.Navn FROM tblResult INNER JOIN tblSeller ON tblResult.SellerID = tblSeller.ID WHERE (((tblSeller.Name)="Robert Smith" ))'

Using escaping:

v_sql = "SELECT DISTINCT tblSeller.ID, tblSeller.Navn FROM tblResult INNER JOIN tblSeller ON tblResult.SellerID = tblSeller.ID WHERE (((tblSeller.Name)=\"Robert Smith\" ))"

or you can use triple quotes (one of ''' or """); these allow for easy multi-line strings as newlines are allowed and included in the final string value:

v_sql = """
SELECT DISTINCT tblSeller.ID, tblSeller.Navn 
FROM tblResult INNER JOIN tblSeller ON tblResult.SellerID = tblSeller.ID
WHERE (((tblSeller.Name)=\"Robert Smith\" ))
"""

answered Dec 10, 2013 at 10:33

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

1 Comment

Enthuziast Over a year ago

I'm really impressed! Thanks!

RickyA · Accepted Answer · 2013-12-10 10:33:30Z

2

Try mixing with single quotes:

v_sql = "SELECT DISTINCT tblSeller.ID, tblSeller.Navn FROM tblResult INNER JOIN tblSeller ON tblResult.SellerID = tblSeller.ID WHERE (((tblSeller.Name)='Robert Smith' ))"

or

v_sql = 'SELECT DISTINCT tblSeller.ID, tblSeller.Navn FROM tblResult INNER JOIN tblSeller ON tblResult.SellerID = tblSeller.ID WHERE (((tblSeller.Name)="Robert Smith" ))'

In python strings can either be quoted with a doublequote " or a singlequote '. This is very usefull in these cases.

answered Dec 10, 2013 at 10:33

RickyA

16.1k6 gold badges77 silver badges97 bronze badges

Comments

Michał Niklas · Accepted Answer · 2013-12-10 10:48:54Z

2

In Python you can create string using ' or " as other says.

But in this case I would like to propose using PreparedStatement instead of simple query. Such PreparedStatement uses ? in place of arguments and arguments are simply array:

v_sql = "SELECT DISTINCT tblSeller.ID, tblSeller.Navn FROM tblResult INNER JOIN tblSeller ON tblResult.SellerID = tblSeller.ID WHERE (((tblSeller.Name)=?))"
rs = c.execute(v_sql, ["Robert Smith", ])

PreparedStatements have many advantages: they are simpler to database engines to parse, and cache query plans, they are SQLInjection attacks safe etc.

answered Dec 10, 2013 at 10:48

Michał Niklas

54.5k19 gold badges76 silver badges125 bronze badges

3 Comments

Enthuziast Over a year ago

WOW! Even an answer to something I wouldn't dream of existed. Thanks!

Enthuziast Over a year ago

Why do you need to add the , after Robert Smith? Does it also take another argument?

Michał Niklas Over a year ago

Yes, it is "open" array, list or tuple ("Robert Smith", ) and you can add more elements. They can be of different types. For tuples with one element , is obligatory and I use it also for lists with one element.

Collectives™ on Stack Overflow

Executing SQL with " " from Python

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related