Pyodbc Execute Command Not Accepting? Parameters Correctly

pyodbc execute command not accepting ? parameters correctly?

Parameter placeholders cannot be used to represent object names (e.g., table or column names) or SQL keywords. They are only used to pass data values, e.g., numbers, strings, dates, etc..

SQL pyodbc issue

You cannot pass column names as query parameters. You would need to concatenate the column names in the query string (while keeping the column values as parameters).

This should look like:

self.cursor.execute(
    "select distinct " 
        + attribute2 
        + " from isap.tn_documentation where " 
        + attribute1 + " = ? and " + attribute2 + " <> ''", 
    i.text(0)
)

Please note that doing so exposes your code to SQL injection: if your attributes input are coming from outside your code, this is a severe security breach. You would need to ensure that they do not contain malicious data (for example by checking the value of each attribute against a fixed list of allowed values: this should be quite easy since we are dealing with column names).

SQL query fails when using pyodbc, but works in SQL

GO is a batch terminator in SQL Server Management Studio. It doesn't make any sense in pyodbc. Instead, issue separate commands from your script.

Updated Code:

import pyodbc, os

def create_db(folder, db_name):
    unc = r'\\arcsql\SDE\{0}'.format(folder)
    if not os.path.exists(unc):
        os.makedirs(unc)
    full_name = os.path.join(r'E:\SDE', folder, db_name)
    conn = pyodbc.connect("driver={SQL Server}; server=ArcSQL; database=master; Trusted_Connection=yes", automcommit=True) 
    cursor = conn.cursor()
    sqlcommand = """
CREATE DATABASE [{0}] ON PRIMARY
( NAME = N'{0}', FILENAME = N'{1}.mdf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 1MB )
LOG ON
( NAME = N'{0}_log', FILENAME = N'{1}_log.ldf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 10%)
""".format(db_name, full_name)
    print sqlcommand

    cursor.execute(sqlcommand)
    print 'Created "{0}"'.format(db_name)

    # Do stuff in the new database
    conn = pyodbc.connect("driver={SQL Server}; server=ArcSQL; database={0}; Trusted_Connection=yes".format(db_name), automcommit=True) 

if __name__ == '__main__':
    #test
    create_db('_test', 'py_db_test')

Update query using python odbc cursor - Not working

Looks like you are missing .commit()

Ex:

cursor2.execute("UPDATE datatable SET data1 = ?, data2 = ?, data3 = ? WHERE id = ?",(d1,d2,d3,i))
cursor2.commit()

---

Related Topics