CODEWITHSUNDEEP
pythonmysqlmysql-python
Harley
Harley

Reputation: 549

How to create a left-join correctly in Python 2.7 with MYSQLdb?

I am trying to join 2 tables with left-join, but something is not oke with it.

I get this error code : "You have an error in your SQL syntax to use near 'EXAMINED_AIRPORT_AIRPORT', \n EXAMINED_AIRPORT_COUNTRY, \nARRIV`' at line 23"

Here my code : 

connection = MySQLdb.connect(host='localhost',
        user='adam',
        passwd='',
        db='database1',
        use_unicode=True,
        charset="utf8")
    cursor = connection.cursor()
    query = """     select 
      `ACTUAL_TIME`, 
      `ACTUAL_DAY`, 
      `EXAMINED_AIRPORT_CODE`, 
      `EXAMINED_AIRPORT_AIRPORT`,     
      `EXAMINED_AIRPORT_COUNTRY`, 
      `ARRIVALS/DEPARTURES`, 
      `SCHEDULED_TIME`, 
      `FLIGHT`, 
      `FROM`, 
      `AIRLANE`,
      `AIRCRAFT`, 
      `STATUS`, 
      `FLIGHT_ID_1`, 
      `FLIGHT_ID_2`, 
      `UPLOAD_TIME`
    from OSSZES as a
    left join (
      select 
        `ACTUAL_TIME`
        `ACTUAL_DAY`,
        COALESCE(`EXAMINED_AIRPORT_CODE`, '') as EXAMINED_AIRPORT_CODE 
        `EXAMINED_AIRPORT_AIRPORT`, 
        `EXAMINED_AIRPORT_COUNTRY`, 
        `ARRIVALS/DEPARTURES`, 
        `SCHEDULED_TIME`, 
        `FLIGHT`, 
        `FROM`, 
        `AIRLANE`,
        `AIRCRAFT`, 
        `STATUS`, 
        `FLIGHT_ID_1`, 
        `FLIGHT_ID_2`, 
        `UPLOAD_TIME`
      from OSSZES_CLEAR
    ) as b using (`ACTUAL_DAY`,`EXAMINED_AIRPORT_CODE`,`FLIGHT`,`SCHEDULED_TIME`,`STATUS`)
    where b.EXAMINED_AIRPORT_CODE is null
    into outfile '/tmp/proba.csv'
    fields terminated by ';'
    enclosed by '"'
    lines terminated by '\n';
        """
    cursor.execute(query)
    connection.commit()
    cursor.close()

Upvotes: 1

Views: 405

Answers (1)

Giulio Bambini
Giulio Bambini

Reputation: 4755

Use a and b for fields. For example if ACTUAL_TIME is from table a( OSSZES), use a.ACTUAL_TIME and same goes for b table.

select 
  `a`.`ACTUAL_TIME`, 
  `a`.`ACTUAL_DAY`, 
  `a`.`EXAMINED_AIRPORT_CODE`, 
  `a`.`EXAMINED_AIRPORT_AIRPORT`,     
  `a`.`EXAMINED_AIRPORT_COUNTRY`, 
  `a`.`ARRIVALS/DEPARTURES`, 
  `a`.`SCHEDULED_TIME`, 
  `a`.`FLIGHT`, 
  `a`.`FROM`, 
  `a`.`AIRLANE`,
  `a`.`AIRCRAFT`, 
  `a`.`STATUS`, 
  `a`.`FLIGHT_ID_1`, 
  `a`.`FLIGHT_ID_2`, 
  `a`.`UPLOAD_TIME`
from OSSZES as a
left join (
  select 
    `ACTUAL_TIME`
    `ACTUAL_DAY`,
    COALESCE(`EXAMINED_AIRPORT_CODE`, '') as EXAMINED_AIRPORT_CODE 
    `EXAMINED_AIRPORT_AIRPORT`, 
    `EXAMINED_AIRPORT_COUNTRY`, 
    `ARRIVALS/DEPARTURES`, 
    `SCHEDULED_TIME`, 
    `FLIGHT`, 
    `FROM`, 
    `AIRLANE`,
    `AIRCRAFT`, 
    `STATUS`, 
    `FLIGHT_ID_1`, 
    `FLIGHT_ID_2`, 
    `UPLOAD_TIME`
  from OSSZES_CLEAR
) as b using (`ACTUAL_DAY`,`EXAMINED_AIRPORT_CODE`,`FLIGHT`,`SCHEDULED_TIME`,`STATUS`)
where `b`.`EXAMINED_AIRPORT_CODE` is null

Upvotes: 2

Related Questions