Merge dataframes by left join SQL & Pandas

55
February 11, 2019, at 07:50 AM

I made two tables into a MySQL database using Python. The following SQL code is to perform join on two tables in the database. How can I do the same by writing equivalent python code?

MySQL code:

SELECT A.num, B.co_name, A.rep_name
FROM A 
JOIN B 
ON A.num=B.no

Desired Python codes:

sql = "XXX"    
df_merged = pd.read_sql(sql, con=cnx)
Answer 1

One approach that you can take is taking each element one by one and inserting into a new table/data frame.

zip( *map(lambda x: pd.read_sql_query(SQL.format(x),connection).loc[0],  
                                      df.yourDataFrame))

This is will generate a key value pair, the SQL table as the key and the pandas df as the value. You can them add these values whatever you like (df, or sql table.)

Hoped this helped :)

Answer 2

I managed to resolve by enclosing my query with appropriate apostrophes:

sql = '''SELECT A.num, B.co_name, A.rep_name 
FROM A 
LEFT JOIN B ON A.num=B.no ''' 
df_merged = pd.read_sql(sql, con=cnx)
READ ALSO
Using an auto_increment sequence for cursor purpose in mysql ? Any other Alternative?

Using an auto_increment sequence for cursor purpose in mysql ? Any other Alternative?

I m creating a application where there are users and posts, and user can likes posts, so i have created a table ,

26
MySQL - Using COALESCE with DATE_ADD and DATE_SUB to get next/previous record

MySQL - Using COALESCE with DATE_ADD and DATE_SUB to get next/previous record

I am trying to query MySQL to select the previous and next recordI need help in using COALESCE and DATE_ADD/DATE_SUB together

43
How to upgrade MySQL database schema in respective to sprint releases?

How to upgrade MySQL database schema in respective to sprint releases?

let say we have a Qt/qml based desktop application, this is using mysql as local db

38