Skip to content
Advertisement

Putting a sql table into python error. Whats wrong?

I would like to know what’s going on with this query. Im not able of put it into a pandas data frame. The error description is “IndexError: tuple index out of range”

  i = 3
  query = ("SELECT COL1, COL2"
  "FROM whatever "
  " where SUBSTR(DATE,1,{}) = '201901' ").format(i)

   df = pd.read_sql(query,con)

The problem is with the format. I need to put the “i” inside the query to take different dates and later ill group them in a dataframe.

Advertisement

Answer

From what I see, you got two problems, the first one is a typo: you forgot to add a space after the concatenation, so it won’t find any columns

i = 3
query = ("SELECT COL1, COL2" # Here you forgot to add a space, the interpreter will read it as COL2FROM
"FROM whatever "
"where SUBSTR(DATE,1,{}) = '201901' ").format(i)

df = pd.read_sql(query,con)

The second thing, what you did with SUBSTR will not return anything at all, because it will only return 3 characters, and you comparing it to 6, so change the i to 6 or make it dynamic. Also, before doing anything on the resulted table, I would suggest you get the length of it, by using the len(df) function, so it should look like this:

i = 6
query = ("SELECT COL1, COL2 "
"FROM whatever "
"where SUBSTR(DATE,1,{}) = '201901' ").format(i)

df = pd.read_sql(query,con)
if len(df):
    print(df[0]) # Prints the first item
else
    print("No items were found")
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement