CODEWITHSUNDEEP
pythonpyodbc
user3654069
user3654069

Reputation: 345

how to add a variable to an SQL query?

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'.")

Upvotes: 2

Views: 1551

Answers (3)

JustBeingHelpful
JustBeingHelpful

Reputation: 18990

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

Upvotes: 0

Aprillion
Aprillion

Reputation: 22340

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

better yet, look at argparse

Upvotes: 0

Martijn Pieters
Martijn Pieters

Reputation: 1125068

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.

Upvotes: 4

Related Questions