Python MongoDB – Create Database

Python MongoDB – Create Database ”; Previous Next Unlike other databases, MongoDB does not provide separate command to create a database. In general, the use command is used to select/switch to the specific database. This command initially verifies whether the database we specify exists, if so, it connects to it. If the database, we specify with the use command doesn’t exist a new database will be created. Therefore, you can create a database in MongoDB using the Use command. Syntax Basic syntax of use DATABASE statement is as follows − use DATABASE_NAME Example Following command creates a database named in mydb. >use mydb switched to db mydb You can verify your creation by using the db command, this displays the current database. >db mydb Creating database using python To connect to MongoDB using pymongo, you need to import and create a MongoClient, then you can directly access the database you need to create in attribute passion. Example Following example creates a database in MangoDB. from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”mydb”] print(“Database created……..”) #Verification print(“List of databases after creating new one”) print(client.list_database_names()) Output Database created…….. List of databases after creating new one: [”admin”, ”config”, ”local”, ”mydb”] You can also specify the port and host names while creating a MongoClient and can access the databases in dictionary style. Example from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”mydb”] print(“Database created……..”) Output Database created…….. Print Page Previous Next Advertisements ”;

Python MongoDB – Find

Python MongoDB – Find ”; Previous Next You can read/retrieve stored documents from MongoDB using the find() method. This method retrieves and displays all the documents in MongoDB in a non-structured way. Syntax Following is the syntax of the find() method. >db.CollectionName.find() Example Assume we have inserted 3 documents into a database named testDB in a collection named sample using the following queries − > use testDB > db.createCollection(“sample”) > data = [ {“_id”: “1001”, “name” : “Ram”, “age”: “26”, “city”: “Hyderabad”}, {“_id”: “1002”, “name” : “Rahim”, “age” : 27, “city” : “Bangalore” }, {“_id”: “1003”, “name” : “Robert”, “age” : 28, “city” : “Mumbai” } ] > db.sample.insert(data) You can retrieve the inserted documents using the find() method as − > use testDB switched to db testDB > db.sample.find() { “_id” : “1001”, “name” : “Ram”, “age” : “26”, “city” : “Hyderabad” } { “_id” : “1002”, “name” : “Rahim”, “age” : 27, “city” : “Bangalore” } { “_id” : “1003”, “name” : “Robert”, “age” : 28, “city” : “Mumbai” } > You can also retrieve first document in the collection using the findOne() method as − > db.sample.findOne() { “_id” : “1001”, “name” : “Ram”, “age” : “26”, “city” : “Hyderabad” } Retrieving data (find) using python The find_One() method of pymongo is used to retrieve a single document based on your query, in case of no matches this method returns nothing and if you doesn’t use any query it returns the first document of the collection. This method comes handy whenever you need to retrieve only one document of a result or, if you are sure that your query returns only one document. Example Following python example retrieve first document of a collection − from pymongo import MongoClient #Creating a pymongo client client = MongoClient(”localhost”, 27017) #Getting the database instance db = client[”mydatabase”] #Creating a collection coll = db[”example”] #Inserting document into a collection data = [ {“_id”: “101”, “name”: “Ram”, “age”: “26”, “city”: “Hyderabad”}, {“_id”: “102”, “name”: “Rahim”, “age”: “27”, “city”: “Bangalore”}, {“_id”: “103”, “name”: “Robert”, “age”: “28”, “city”: “Mumbai”} ] res = coll.insert_many(data) print(“Data inserted ……”) print(res.inserted_ids) #Retrieving the first record using the find_one() method print(“First record of the collection: “) print(coll.find_one()) #Retrieving a record with is 103 using the find_one() method print(“Record whose id is 103: “) print(coll.find_one({“_id”: “103”})) Output Data inserted …… [”101”, ”102”, ”103”] First record of the collection: {”_id”: ”101”, ”name”: ”Ram”, ”age”: ”26”, ”city”: ”Hyderabad”} Record whose id is 103: {”_id”: ”103”, ”name”: ”Robert”, ”age”: ”28”, ”city”: ”Mumbai”} To get multiple documents in a single query (single call od find method), you can use the find() method of the pymongo. If haven’t passed any query, this returns all the documents of a collection and, if you have passed a query to this method, it returns all the matched documents. Example #Getting the database instance db = client[”myDB”] #Creating a collection coll = db[”example”] #Inserting document into a collection data = [ {“_id”: “101”, “name”: “Ram”, “age”: “26”, “city”: “Hyderabad”}, {“_id”: “102”, “name”: “Rahim”, “age”: “27”, “city”: “Bangalore”}, {“_id”: “103”, “name”: “Robert”, “age”: “28”, “city”: “Mumbai”} ] res = coll.insert_many(data) print(“Data inserted ……”) #Retrieving all the records using the find() method print(“Records of the collection: “) for doc1 in coll.find(): print(doc1) #Retrieving records with age greater than 26 using the find() method print(“Record whose age is more than 26: “) for doc2 in coll.find({“age”:{“$gt”:”26″}}): print(doc2) Output Data inserted …… Records of the collection: {”_id”: ”101”, ”name”: ”Ram”, ”age”: ”26”, ”city”: ”Hyderabad”} {”_id”: ”102”, ”name”: ”Rahim”, ”age”: ”27”, ”city”: ”Bangalore”} {”_id”: ”103”, ”name”: ”Robert”, ”age”: ”28”, ”city”: ”Mumbai”} Record whose age is more than 26: {”_id”: ”102”, ”name”: ”Rahim”, ”age”: ”27”, ”city”: ”Bangalore”} {”_id”: ”103”, ”name”: ”Robert”, ”age”: ”28”, ”city”: ”Mumbai”} Print Page Previous Next Advertisements ”;

Python SQLite – Join

Python SQLite – Join ”; Previous Next When you have divided the data in two tables you can fetch combined records from these two tables using Joins. Example Assume we have created a table with name CRICKETERS using the following query − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> Let us create one more table OdiStats describing the One-day cricket statistics of each player in CRICKETERS table. sqlite> CREATE TABLE ODIStats ( First_Name VARCHAR(255), Matches INT, Runs INT, AVG FLOAT, Centuries INT, HalfCenturies INT ); sqlite> Following statement retrieves data combining the values in these two tables − sqlite> SELECT Cricketers.First_Name, Cricketers.Last_Name, Cricketers.Country, OdiStats.matches, OdiStats.runs, OdiStats.centuries, OdiStats.halfcenturies from Cricketers INNER JOIN OdiStats ON Cricketers.First_Name = OdiStats.First_Name; First_Name Last_Name Country Matches Runs Centuries HalfCenturies ———- ———- ——- ———- ————- ———- ———- Shikhar Dhawan Indi 133 5518 17 27 Jonathan Trott Sout 68 2819 4 22 Kumara Sangakkara Sril 404 14234 25 93 Virat Kohli Indi 239 11520 43 54 Rohit Sharma Indi 218 8686 24 42 sqlite> Join clause using python Following SQLite example, demonstrates the JOIN clause using python − import sqlite3 #Connecting to sqlite conn = sqlite3.connect(”example.db”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving data sql = ”””SELECT * from EMP INNER JOIN CONTACT ON EMP.CONTACT = CONTACT.ID””” #Executing the query cursor.execute(sql) #Fetching 1st row from the table result = cursor.fetchall(); print(result) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output [(”Ramya”, ”Rama priya”, 27, ”F”, 9000.0, 101, 101, ”[email protected]”, ”Hyderabad”), (”Vinay”, ”Battacharya”, 20, ”M”, 6000.0, 102, 102,”[email protected]”, ”Vishakhapatnam”), (”Sharukh”, ”Sheik”, 25, ”M”, 8300.0, 103, 103, ”[email protected]”, ”Pune”), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0, 104, 104, ”[email protected]”, ”Mumbai”)] Print Page Previous Next Advertisements ”;

Python MySQL – Create Database

Python MySQL – Create Database ”; Previous Next You can create a database in MYSQL using the CREATE DATABASE query. Syntax Following is the syntax of the CREATE DATABASE query − CREATE DATABASE name_of_the_database Example Following statement creates a database with name mydb in MySQL − mysql> CREATE DATABASE mydb; Query OK, 1 row affected (0.04 sec) If you observe the list of databases using the SHOW DATABASES statement, you can observe the newly created database in it as shown below − mysql> SHOW DATABASES; +——————–+ | Database | +——————–+ | information_schema | | logging | | mydatabase | | mydb | | performance_schema | | students | | sys | +——————–+ 26 rows in set (0.15 sec) Creating a database in MySQL using python After establishing connection with MySQL, to manipulate data in it you need to connect to a database. You can connect to an existing database or, create your own. You would need special privileges to create or to delete a MySQL database. So if you have access to the root user, you can create any database. Example Following example establishes connection with MYSQL and creates a database in it. import mysql.connector #establishing the connection conn = mysql.connector.connect(user=”root”, password=”password”, host=”127.0.0.1”) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping database MYDATABASE if already exists. cursor.execute(“DROP database IF EXISTS MyDatabase”) #Preparing query to create a database sql = “CREATE database MYDATABASE”; #Creating a database cursor.execute(sql) #Retrieving the list of databases print(“List of databases: “) cursor.execute(“SHOW DATABASES”) print(cursor.fetchall()) #Closing the connection conn.close() Output List of databases: [(”information_schema”,), (”dbbug61332”,), (”details”,), (”exampledatabase”,), (”mydatabase”,), (”mydb”,), (”mysql”,), (”performance_schema”,)] Print Page Previous Next Advertisements ”;

Python MySQL – Cursor Object

Python MySQL – Cursor Object ”; Previous Next The MySQLCursor of mysql-connector-python (and similar libraries) is used to execute statements to communicate with the MySQL database. Using the methods of it you can execute SQL statements, fetch data from the result sets, call procedures. You can create Cursor object using the cursor() method of the Connection object/class. Example import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb” ) #Creating a cursor object using the cursor() method cursor = conn.cursor() Methods Following are the various methods provided by the Cursor class/object. Sr.No Method & Description 1 callproc() This method is used to call existing procedures MySQL database. 2 close() This method is used to close the current cursor object. 3 Info() This method gives information about the last query. 4 executemany() This method accepts a list series of parameters list. Prepares an MySQL query and executes it with all the parameters. 5 execute() This method accepts a MySQL query as a parameter and executes the given query. 6 fetchall() This method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones) 7 fetchone() This method fetches the next row in the result of a query and returns it as a tuple. 8 fetchmany() This method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row. 9 etchwarnings() This method returns the warnings generated by the last executed query. Properties Following are the properties of the Cursor class − Sr.No Property & Description 1 column_names This is a read only property which returns the list containing the column names of a result-set. 2 description This is a read only property which returns the list containing the description of columns in a result-set. 3 lastrowid This is a read only property, if there are any auto-incremented columns in the table, this returns the value generated for that column in the last INSERT or, UPDATE operation. 4 rowcount This returns the number of rows returned/updated in case of SELECT and UPDATE operations. 5 statement This property returns the last executed statement. Print Page Previous Next Advertisements ”;

Python PostgreSQL – Introduction

Python PostgreSQL – Introduction ”; Previous Next Installation PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development phase and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. To communicate with PostgreSQL using Python you need to install psycopg, an adapter provided for python programming, the current version of this is psycog2. psycopg2 was written with the aim of being very small and fast, and stable as a rock. It is available under PIP (package manager of python) Installing Psycog2 using PIP First of all, make sure python and PIP is installed in your system properly and, PIP is up-to-date. To upgrade PIP, open command prompt and execute the following command − C:UsersTutorialspoint>python -m pip install –upgrade pip Collecting pip Using cached https://files.pythonhosted.org/packages/8d/07/f7d7ced2f97ca3098c16565efbe6b15fafcba53e8d9bdb431e09140514b0/pip-19.2.2-py2.py3-none-any.whl Installing collected packages: pip Found existing installation: pip 19.0.3 Uninstalling pip-19.0.3: Successfully uninstalled pip-19.0.3 Successfully installed pip-19.2.2 Then, open command prompt in admin mode and execute the pip install psycopg2-binary command as shown below − C:WINDOWSsystem32>pip install psycopg2-binary Collecting psycopg2-binary Using cached https://files.pythonhosted.org/packages/80/79/d0d13ce4c2f1addf4786f4a2ded802c2df66ddf3c1b1a982ed8d4cb9fc6d/psycopg2_binary-2.8.3-cp37-cp37m-win32.whl Installing collected packages: psycopg2-binary Successfully installed psycopg2-binary-2.8.3 Verification To verify the installation, create a sample python script with the following line in it. import mysql.connector If the installation is successful, when you execute it, you should not get any errors − D:Python_PostgreSQL>import psycopg2 D:Python_PostgreSQL> Print Page Previous Next Advertisements ”;

Python PostgreSQL – Select Data

Python PostgreSQL – Select Data ”; Previous Next You can retrieve the contents of an existing table in PostgreSQL using the SELECT statement. At this statement, you need to specify the name of the table and, it returns its contents in tabular format which is known as result set. Syntax Following is the syntax of the SELECT statement in PostgreSQL − SELECT column1, column2, columnN FROM table_name; Example Assume we have created a table with name CRICKETERS using the following query − postgres=# CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); CREATE TABLE postgres=# And if we have inserted 5 records in to it using INSERT statements as − postgres=# insert into CRICKETERS values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); INSERT 0 1 postgres=# insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); INSERT 0 1 Following SELECT query retrieves the values of the columns FIRST_NAME, LAST_NAME and, COUNTRY from the CRICKETERS table. postgres=# SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS; first_name | last_name | country ————+————+————- Shikhar | Dhawan | India Jonathan | Trott | SouthAfrica Kumara | Sangakkara | Srilanka Virat | Kohli | India Rohit | Sharma | India (5 rows) If you want to retrieve all the columns of each record you need to replace the names of the columns with “*” as shown below − postgres=# SELECT * FROM CRICKETERS; first_name | last_name | age | place_of_birth | country ————+————+—–+—————-+————- Shikhar | Dhawan | 33 | Delhi | India Jonathan | Trott | 38 | CapeTown | SouthAfrica Kumara | Sangakkara | 41 | Matale | Srilanka Virat | Kohli | 30 | Delhi | India Rohit | Sharma | 32 | Nagpur | India (5 rows) postgres=# Retrieving data using python READ Operation on any database means to fetch some useful information from the database. You can fetch data from PostgreSQL using the fetch() method provided by the psycopg2. The Cursor class provides three methods namely fetchall(), fetchmany() and, fetchone() where, The fetchall() method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows, it returns the remaining ones). The fetchone() method fetches the next row in the result of a query and returns it as a tuple. The fetchmany() method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row. Note − A result set is an object that is returned when a cursor object is used to query a table. Example The following Python program connects to a database named mydb of PostgreSQL and retrieves all the records from a table named EMPLOYEE. import psycopg2 #establishing the connection conn = psycopg2.connect( database=”mydb”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Setting auto commit false conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving data cursor.execute(”””SELECT * from EMPLOYEE”””) #Fetching 1st row from the table result = cursor.fetchone(); print(result) #Fetching 1st row from the table result = cursor.fetchall(); print(result) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output (”Ramya”, ”Rama priya”, 27, ”F”, 9000.0) [(”Vinay”, ”Battacharya”, 20, ”M”, 6000.0), (”Sharukh”, ”Sheik”, 25, ”M”, 8300.0), (”Sarmista”, ”Sharma”, 26, ”F”, 10000.0), (”Tripthi”, ”Mishra”, 24, ”F”, 6000.0)] Print Page Previous Next Advertisements ”;

Python MongoDB – Introduction

Python MongoDB – Introduction ”; Previous Next Pymongo is a python distribution which provides tools to work with MongoDB, it is the most preferred way to communicate with MongoDB database from python. Installation To install pymongo first of all make sure you have installed python3 (along with PIP) and MongoDB properly. Then execute the following command. C:WINDOWSsystem32>pip install pymongo Collecting pymongo Using cached https://files.pythonhosted.org/packages/cb/a6/b0ae3781b0ad75825e00e29dc5489b53512625e02328d73556e1ecdf12f8/pymongo-3.9.0-cp37-cp37m-win32.whl Installing collected packages: pymongo Successfully installed pymongo-3.9.0 Verification Once you have installed pymongo, open a new text document, paste the following line in it and, save it as test.py. import pymongo If you have installed pymongo properly, if you execute the test.py as shown below, you should not get any issues. D:Python_MongoDB>test.py D:Python_MongoDB> Print Page Previous Next Advertisements ”;

Python SQLite – Order By

Python SQLite – Order By ”; Previous Next While fetching data using SELECT query, you will get the records in the same order in which you have inserted them. You can sort the results in desired order (ascending or descending) using the Order By clause. By default, this clause sorts results in ascending order, if you need to arrange them in descending order you need to use “DESC” explicitly. Syntax Following is the syntax of the ORDER BY clause in SQLite. SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; Example Assume we have created a table with name CRICKETERS using the following query − sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite> And if we have inserted 5 records in to it using INSERT statements as − sqlite> insert into CRICKETERS values(”Shikhar”, ”Dhawan”, 33, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Jonathan”, ”Trott”, 38, ”CapeTown”, ”SouthAfrica”); sqlite> insert into CRICKETERS values(”Kumara”, ”Sangakkara”, 41, ”Matale”, ”Srilanka”); sqlite> insert into CRICKETERS values(”Virat”, ”Kohli”, 30, ”Delhi”, ”India”); sqlite> insert into CRICKETERS values(”Rohit”, ”Sharma”, 32, ”Nagpur”, ”India”); sqlite> Following SELECT statement retrieves the rows of the CRICKETERS table in the ascending order of their age − sqlite> SELECT * FROM CRICKETERS ORDER BY AGE; First_Name Last_Name Age Place_Of_B Country ———- ———- —- ———- ————- Virat Kohli 30 Delhi India Rohit Sharma 32 Nagpur India Shikhar Dhawan 33 Delhi India Jonathan Trott 38 CapeTown SouthAfrica Kumara Sangakkara 41 Matale Srilanka sqlite> You can use more than one column to sort the records of a table. Following SELECT statements sorts the records of the CRICKETERS table based on the columns AGE and FIRST_NAME. sqlite> SELECT * FROM CRICKETERS ORDER BY AGE, FIRST_NAME; First_Name Last_Name Age Place_Of_B Country ———- ———- —- ———- ————- Virat Kohli 30 Delhi India Rohit Sharma 32 Nagpur India Shikhar Dhawan 33 Delhi India Jonathan Trott 38 CapeTown SouthAfrica Kumara Sangakkara 41 Matale Srilanka sqlite> By default, the ORDER BY clause sorts the records of a table in ascending order you can arrange the results in descending order using DESC as − sqlite> SELECT * FROM CRICKETERS ORDER BY AGE DESC; First_Name Last_Name Age Place_Of_B Country ———- ———- —- ———- ————- Kumara Sangakkara 41 Matale Srilanka Jonathan Trott 38 CapeTown SouthAfrica Shikhar Dhawan 33 Delhi India Rohit Sharma 32 Nagpur India Virat Kohli 30 Delhi India sqlite> ORDER BY clause using python To retrieve contents of a table in specific order, invoke the execute() method on the cursor object and, pass the SELECT statement along with ORDER BY clause, as a parameter to it. Example In the following example we are creating a table with name and Employee, populating it, and retrieving its records back in the (ascending) order of their age, using the ORDER BY clause. import psycopg2 #establishing the connection conn = psycopg2.connect( database=”mydb”, user=”postgres”, password=”password”, host=”127.0.0.1”, port= ”5432” ) #Setting auto commit false conn.autocommit = True #Creating a cursor object using the cursor() method cursor = conn.cursor() #Doping EMPLOYEE table if already exists. cursor.execute(“DROP TABLE IF EXISTS EMPLOYEE”) #Creating a table sql = ”””CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME INT, CONTACT INT )””” cursor.execute(sql) #Populating the table #Populating the table cursor.execute(”””INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES (”Ramya”, ”Rama priya”, 27, ”F”, 9000), (”Vinay”, ”Battacharya”, 20, ”M”, 6000), (”Sharukh”, ”Sheik”, 25, ”M”, 8300), (”Sarmista”, ”Sharma”, 26, ”F”, 10000), (”Tripthi”, ”Mishra”, 24, ”F”, 6000)”””) conn.commit() #Retrieving specific records using the ORDER BY clause cursor.execute(“SELECT * from EMPLOYEE ORDER BY AGE”) print(cursor.fetchall()) #Commit your changes in the database conn.commit() #Closing the connection conn.close() Output [(”Vinay”, ”Battacharya”, 20, ”M”, 6000, None), (”Tripthi”, ”Mishra”, 24, ”F”, 6000, None), (”Sharukh”, ”Sheik”, 25, ”M”, 8300, None), (”Sarmista”, ”Sharma”, 26, ”F”, 10000, None), (”Ramya”, ”Rama priya”, 27, ”F”, 9000, None)] Print Page Previous Next Advertisements ”;

Python MySQL – Join

Python MySQL – Join ”; Previous Next When you have divided the data in two tables you can fetch combined records from these two tables using Joins. Example Suppose we have created a table with name EMPLOYEE and populated data into it as shown below − mysql> CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT, CONTACT INT ); Query OK, 0 rows affected (0.36 sec) INSERT INTO Employee VALUES (”Ramya”, ”Rama Priya”, 27, ”F”, 9000, 101), (”Vinay”, ”Bhattacharya”, 20, ”M”, 6000, 102), (”Sharukh”, ”Sheik”, 25, ”M”, 8300, 103), (”Sarmista”, ”Sharma”, 26, ”F”, 10000, 104), (”Trupthi”, ”Mishra”, 24, ”F”, 6000, 105); Query OK, 5 rows affected (0.08 sec) Records: 5 Duplicates: 0 Warnings: 0 Then, if we have created another table and populated it as − CREATE TABLE CONTACT( ID INT NOT NULL, EMAIL CHAR(20) NOT NULL, PHONE LONG, CITY CHAR(20) ); Query OK, 0 rows affected (0.49 sec) INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES (101, ”[email protected]”, ”Hyderabad”), (102, ”[email protected]”, ”Vishakhapatnam”), (103, ”[email protected]”, ”Pune”), (104, ”[email protected]”, ”Mumbai”); Query OK, 4 rows affected (0.10 sec) Records: 4 Duplicates: 0 Warnings: 0 Following statement retrieves data combining the values in these two tables − mysql> SELECT * from EMPLOYEE INNER JOIN CONTACT ON EMPLOYEE.CONTACT = CONTACT.ID; +————+————–+——+——+——–+———+—–+——————–+——-+—————-+ | FIRST_NAME | LAST_NAME | AGE | SEX | INCOME | CONTACT | ID | EMAIL | PHONE | CITY | +————+————–+——+——+——–+———+—–+——————–+——-+—————-+ | Ramya | Rama Priya | 27 | F | 9000 | 101 | 101 | [email protected] | NULL | Hyderabad | | Vinay | Bhattacharya | 20 | M | 6000 | 102 | 102 | [email protected] | NULL | Vishakhapatnam | | Sharukh | Sheik | 25 | M | 8300 | 103 | 103 | [email protected] | NULL | Pune | | Sarmista | Sharma | 26 | F | 10000 | 104 | 104 | [email protected] | NULL | Mumbai | +————+————–+——+——+——–+———+—–+——————–+——-+—————-+ 4 rows in set (0.00 sec) MYSQL JOIN using python Following example retrieves data from the above two tables combined by contact column of the EMPLOYEE table and ID column of the CONTACT table. import mysql.connector #establishing the connection conn = mysql.connector.connect( user=”root”, password=”password”, host=”127.0.0.1”, database=”mydb” ) #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving single row sql = ”””SELECT * from EMPLOYEE INNER JOIN CONTACT ON EMPLOYEE.CONTACT = CONTACT.ID””” #Executing the query cursor.execute(sql) #Fetching 1st row from the table result = cursor.fetchall(); print(result) #Closing the connection conn.close() Output [(”Krishna”, ”Sharma”, 26, ”M”, 2000, 101, 101, ”[email protected]”, 9848022338, ”Hyderabad”), (”Raj”, ”Kandukuri”, 20, ”M”, 7000, 102, 102, ”[email protected]”, 9848022339, ”Vishakhapatnam”), (”Ramya”, ”Ramapriya”, 29, ”F”, 5000, 103, 103, ”[email protected]”, 9848022337, ”Pune”), (”Mac”, ”Mohan”, 26, ”M”, 2000, 104, 104, ”[email protected]”, 9848022330, ”Mumbai”)] Print Page Previous Next Advertisements ”;