Trouble With SQL Query in Python

Hello I’m getting an error: near “join”: syntax error. Is there an obvious issue with this that I’m not picking up on? I’ve changed names in the query but I’ve gone over and checked for spelling errors already.

import pandas as pd
import sqlite3 as sql

path1 = r'C:file.xlsx'
path2 = r'C:file2.xlsx'
tenants = pd.read_excel(path1, sheet_name='1')
buildings = pd.read_excel(path2)

db = sql.connect('temp.db')

tenants.to_sql('tenantsdb', db)
buildings.to_sql('buildingsdb', db)

Query = pd.read_sql("select t.*, b.distance from tenantsdb t where city = 'city' join buildingsdb b on t.Address = b.Street_Address;", db)

db.close()

Answer

In SQL, the order of clauses is SELECT, FROM, JOIN, WHERE. You have JOIN in the wrong place.

Query = pd.read_sql("""
    select t.*, b.distance 
    from tenantsdb t 
    join buildingsdb b on t.Address = b.Street_Address 
    where city = 'city';""", db)

Advertisement

Answer