How to call a complex SQL query in python

I have the following SQL code:

select * from dbo.dictionary;
select d1.literal_id, d1.text as spanish , d2.text as english,d1.id as 'id(spanish)',d2.id as 'id(english)' from dictionary d1
left join dictionary d2
on d1.literal_id = d2.literal_id
and d2.lenguaje =223622
where d1.lenguaje =223621;

This SQL code has been tested and runs perfectly on SQL Server Management Studio.

Now I have the following code for python which is to communicate with an MS SQL Server instance:

import pandas as pd
import pyodbc

server = 'my-server-name'
database = 'my_db_name'
username = 'MYUSERNAME'
password = 'MYPASSWORD'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

df = pd.read_sql_query('select * from dbo.dictionary;
select d1.literal_id, d1.text as spanish , d2.text as english,d1.id as 'id(spanish)',d2.id as 'id(english)' from dictionary d1
left join dictionary d2
on d1.literal_id = d2.literal_id
and d2.lenguaje =223622
where d1.lenguaje =223621;', cnxn)

print(df)

When I run it, I get the following error

EOL while scanning string literal

where it points to the first semicolon in my code. Could you show me how I may get this python code to run?

Answer

Fixing the quotes and removing the select statement, and I’m pretty sure the last two lines of your SQL got swapped. It should be WHERE…AND, not AND…WHERE. Like this:

import pandas as pd
import pyodbc

server = 'my-server-name'
database = 'my_db_name'
username = 'MYUSERNAME'
password = 'MYPASSWORD'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

df = pd.read_sql_query("""
SELECT d1.literal_id, d1.text as spanish, d2.text as english,
       d1.id as 'id(spanish)',d2.id as 'id(english)'
FROM dictionary d1
   LEFT JOIN dictionary d2
      ON d1.literal_id = d2.literal_id
WHERE d1.lenguaje = 223621
      AND d2.lenguaje = 223622;""", cnxn)

print(df)