0

I'm passing data to database for querying the database but I have run into following error.

 >>> my_cursor.execute(my_query,  var1)
 Traceback (most recent call last):
   File "<stdin>", line 1, in <module>
 pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

my codes are 

  import pyodbc
  import pandas as pd

  var1 = 'UCSB'
  my_connection = pyodbc.connect('DSN=MapR-64-Hive', autocommit=True)
  my_cursor = my_connection.cursor()
  my_query =  """
      select *  from polaris_datasets.snmaster  where parent_pid rlike '%s' and created_date >=  "2017-12-01 00:00:00"
      """ 

  my_cursor.execute(my_query,  var1)

Does anyone know what would cause this issue?

Joohun Lee
  • 187
  • 2
  • 14
  • 1
    Yes. `%s` is not a parameter marker, so your SQL contains zero parameter markers but you're passing one in `var1`.. Read the words in the error message, the code you posted, and any of the dozens of existing questions here with similar error messages, several of which are in the **Related** list over there **====>>>>**. – Ken White Jan 11 '18 at 02:10
  • 1
    `?` is the parameter marker, not `%s`. – John Gordon Jan 11 '18 at 02:29
  • in fact, I already checked multiple similar issues posted and they suggested to use ? as parameter marker. I have also tried but it failed and return with error message "pyodbc.Error:(.... Syntax or semantic analysis error ...Encountered:Unexpected character\nExpected:" . I was wondering if there's any small mistake I made in my code – Joohun Lee Jan 11 '18 at 02:31

2 Answers2

0

I found the issue from my codes and I'm answering my own question. the code should be changed in this way and it works for me. I'm not sure why ? is not consumed in my case.

 import pyodbc
 import pandas as pd

 var1 = 'UCSB'
 my_connection = pyodbc.connect('DSN=MapR-64-Hive', autocommit=True)
 my_cursor = my_connection.cursor()
 my_query =  """
     select *  from polaris_datasets.snmaster  where parent_pid rlike %s and created_date >=  "2017-12-01 00:00:00"
     """ 

 my_cursor.execute(my_query % var1)
Joohun Lee
  • 187
  • 2
  • 14
  • The ? parameter markers or the other types (e.g. `:colname`) are not supported with the Hive ODBC driver. Unfortunately, the current solution which you post has been the only way to get "parametrized queries" to work. The downside is that this invites SQL injection attacks. – Scratch'N'Purr Feb 19 '18 at 10:10
  • I am experiencing this issue yet the workaround did not work. It results in the following error. ProgrammingError: ('42000', "[42000] [Cloudera][ImpalaODBC] (360) Syntax error occurred during query execution: [HY000] : AnalysisException: Could not resolve column/field reference: 'UCSB' \n (360) (SQLExecDirectW)") – OTM Jun 24 '21 at 20:25
0

The solution provided above will perform string substitution of var1 and issue the query with no parameterization - this is bad and leaves you open to SQL injection.

Try this tweak, just by substituting in the ? parameter marker, and see this answer for some more information:

  import pyodbc
  import pandas as pd

  var1 = 'UCSB'
  my_connection = pyodbc.connect('DSN=MapR-64-Hive', autocommit=True)
  my_cursor = my_connection.cursor()
  my_query =  """select * from polaris_datasets.snmaster where parent_pid rlike ? and created_date >= '2017-12-01 00:00:00'""" 

  my_cursor.execute(my_query, var1)
bsplosion
  • 2,641
  • 27
  • 38