how to add a variable to an SQL query?

Question

I am trying to add argv[0] as variable to the SQL query below and running into compilation error below,what is the syntax to fix this?

#!/usr/bin/python
import pypyodbc as pyodbc
from sys import argv
component_id=argv[0]
server_name='odsdb.company.com'
database_name='ODS'

    cnx = pyodbc.connect("DRIVER={SQL Server};SERVER="+server_name+";DATABASE="+database_name)
    db_cursor=cnx.cursor()
    SQL = 'SELECT Top 1 cr.ReleaseLabel ' + \
    'FROM [ODS].[v000001].[ComponentRevisions] cr ' + \
    'WHERE cr.ComponentId=' + component_id + \
    'ORDER BY cr.CreatedOn DESC' 
    resp_rows_obj=db_cursor.execute(SQL)
    print '('+', '.join([column_heading_tuple[0] for column_heading_tuple in resp_rows_obj.description])+')'
    for row in resp_rows_obj:
        print row

Error:-

pypyodbc.ProgrammingError: (u'42000', u"[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'BY'.")

Have you actually tried printing SQL to see what it looks like? — jonrsharpe
– jonrsharpe, Commented May 28, 2014 at 22:09
@jonrsharpe: common enough mistake, thinking you can just put values into the string. Solution: teach about SQL parameters. — Martijn Pieters
– Martijn Pieters, Commented May 28, 2014 at 22:12

Martijn Pieters · Accepted Answer · 2014-05-28 22:21:18Z

4

Don't use string interpolation. Use SQL parameters; these are placeholders in the query where your database will insert values:

SQL = '''\
    SELECT Top 1 cr.ReleaseLabel
    FROM [ODS].[v000001].[ComponentRevisions] cr
    WHERE cr.ComponentId = ?
    ORDER BY cr.CreatedOn DESC
'''
resp_rows_obj = db_cursor.execute(SQL, (component_id,))

Values for the ? placeholders are sourced from the second argument to the cursor.execute() function, a sequence of values. Here you only have one value, so I used a one-element tuple.

Note that you probably want argv[1], not argv[0]; the latter is the script name, not the first argument.

edited May 28, 2014 at 22:21

answered May 28, 2014 at 22:10

Martijn Pieters

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

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

2 Comments

user3654069 Over a year ago

@i get an error pypyodbc.DataError: (u'22018', u"[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the varchar value 'odsdb.py' to data type int.")

Martijn Pieters Over a year ago

@user3654069: Ah, yes, you are trying to pass in the script name.

Aprillion · Accepted Answer · 2014-05-28 22:27:08Z

0

to retrieve 1st command line argument do component_id=argv[1] instead of 0 which is the script name...

better yet, look at argparse

answered May 28, 2014 at 22:27

Aprillion

22.4k6 gold badges59 silver badges94 bronze badges

Comments

JustBeingHelpful · Accepted Answer · 2014-09-22 21:04:37Z

0

We had a hyphen in the database name that was being used in a T-SQL query being called from Python code. So we just added square brackets because SQL Server cannot interpolate the hyphen without them.

Before:

SELECT * FROM DBMS-NAME.dbo.TABLE_NAME

After:

SELECT * FROM [DBMS-NAME].dbo.TABLE_NAME

answered Sep 22, 2014 at 21:04

JustBeingHelpful

19.1k39 gold badges169 silver badges254 bronze badges

Collectives™ on Stack Overflow

how to add a variable to an SQL query?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related