Search code examples
pythonmysqlmysql-connectormysql-connector-python

MySQL Connector not working with table aliases

I'm using mysql.connector in python script. When I'm trying to execute script that has aliases for tables - it cannot be executed and gives error "unknown column 'column_name' in 'field_list'".

Both tables have fields with the same names, so I can't just not use aliases.

Code:

query = ("SELECT ti.itemno, ti.active FROM text_item AS ti"
         "INNER JOIN text_itemmember AS tim ON tim.itemno = ti.itemno;")
cursor.execute(query)

The same error I get when replace alias "ti" on table name ("text_item"). I can't find any information about connector not working with aliases.

How do I fix this?

Solution

  • You need a space after the table alias, e.g:

    query = ("SELECT ti.itemno, ti.active FROM text_item AS ti "
         "INNER JOIN text_itemmember AS tim ON tim.itemno = ti.itemno")
cursor.execute(query)

    Python combines adjacent string literals into one, so you actually have 'tiINNER' as the alias name.