Home >>Python Tutorial >Python Mysql Join Query

Python Mysql Join Query

To Join Two or More Tables

This is the python mysql inner join module of the entire Python MySQL tutorial. And while using this programming language, if you wish to combine rows from any particular two or more tables on the basis of related columns between them then you can do that too. And to accomplish that task you need to use the ‘JOIN’ statement. Now to understand this concept better you should consider two different tables. Let the name of one of those tables be ‘users’ and the name of the other table be ‘products’.

The Users Table is Mentioned below.

{ id : 1, name : ‘John’, fav : 154 }, { id : 2, name : ‘Peter’, fav : 154 }, { id : 3, name : ‘Amy’, fav : 154 }, { id : 4, name : ‘Hannah’, fav : }, { id : 5, name : ‘Michael’, fav : }

The Products Table is also mentioned below.

{ id : 154, name : ‘Chocolate Heaven’ }, { id : 155, name : ‘Tasty Lemons’ }, { id : 156, name : ‘Vanilla Dreams’ }, If you wish to combine both of these tables then you can do that by combining the fav field of the users table and the id field of the products table. If you wish to join the users and products table to be able to see the favorite products of the specific users then the python mysql join query example for this is mentioned below.

import mysql.connector

mydb = mysql.connector.connect (

  host=“localhost” ,

  user=“yourusername” ,

  passwd=“yourpassword” ,

  database=“mydatabase” ,

)

mycursor = mydb.cursor ( )

sql = “SELECT \

    users.name AS user, \

    products.name AS favorite \

    FROM users \

    INNER JOIN products ON users.fav = products.id”

mycursor.execute ( sql )

myresult = mycursor.fetchall ( )

for x in myresult :

   print ( x )

It is also important for you to remember that you can use the JOIN keyword instead of the INNER JOIN statement. These both statements will yield the same result.

Python Mysql - Left Join

In the example that is mentioned above, you might have noticed that the names Hannah and Michael were excluded from the results that were obtained at last. This happened because of the fact that the INNER JOIN statement only shows the record that actually come up with a match. However, if you wish to show all the statements even the ones that do not show a match then it is recommended that you should use the python mysql LEFT JOIN statement. For example, if you wish to select all the users and their favorite products then

sql = “SELECT \

    users.name AS user, \

    products.name AS favorite \

    FROM users \

    LEFT JOIN products ON users.fav = products.id”

This is the example for the python mysql left join statement.

Python Mysql - Right Join

If you want to have all the data of the products and the users who have those products as their favorite or users that do not have any product as their favorite returned to you then you can do that too. To accomplish this task it is recommended that you should use the RIGHT JOIN statement. If you wish to select all the products and the users that have those products as their favorite then the python mysql join query example for this is mentioned below.

sql = “SELECT \

   users.name AS user, \

   products.name As favorite \

   FROM users \

   RIGHT JOIN products ON users.fav = products.id”

It is also important for you to note that the users named ‘Hannah’ and ‘Michael’ who do not have any favorite products are not included in this list. With this, we finish the python mysql inner join part of our Python MySQL Tutorial.

« Previous Next »