Python Data Access Tutorial
Selected Reading
- Python Data Access - Discussion
- Python Data Access - Useful Resources
- Python Data Access - Quick Guide
- Python MongoDB - Limit
- Python MongoDB - Update
- Python MongoDB - Drop Collection
- Python MongoDB - Delete Document
- Python MongoDB - Sort
- Python MongoDB - Query
- Python MongoDB - Find
- Python MongoDB - Insert Document
- Python MongoDB - Create Collection
- Python MongoDB - Create Database
- Python MongoDB - Introduction
- Python SQLite - Cursor Object
- Python SQLite - Join
- Python SQLite - Limit
- Python SQLite - Drop Table
- Python SQLite - Delete Data
- Python SQLite - Update Table
- Python SQLite - Order By
- Python SQLite - Where Clause
- Python SQLite - Select Data
- Python SQLite - Insert Data
- Python SQLite - Create Table
- Python SQLite - Establishing Connection
- Python SQLite - Introduction
- Python PostgreSQL - Cursor Object
- Python PostgreSQL - Join
- Python PostgreSQL - Limit
- Python PostgreSQL - Drop Table
- Python PostgreSQL - Delete Data
- Python PostgreSQL - Update Table
- Python PostgreSQL - Order By
- Python PostgreSQL - Where Clause
- Python PostgreSQL - Select Data
- Python PostgreSQL - Insert Data
- Python PostgreSQL - Create Table
- Python PostgreSQL - Create Database
- Python PostgreSQL - Database Connection
- Python PostgreSQL - Introduction
- Python MySQL - Cursor Object
- Python MySQL - Join
- Python MySQL - Limit
- Python MySQL - Drop Table
- Python MySQL - Delete Data
- Python MySQL - Update Table
- Python MySQL - Order By
- Python MySQL - Where Clause
- Python MySQL - Select Data
- Python MySQL - Insert Data
- Python MySQL - Create Table
- Python MySQL - Create Database
- Python MySQL - Database Connection
- Python MySQL - Introduction
- Python Data Access - Home
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
Python SQLite - Join
Python SQLite - Join
When you have spanided 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 −
sqpte> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqpte>
Let us create one more table OdiStats describing the One-day cricket statistics of each player in CRICKETERS table.
sqpte> CREATE TABLE ODIStats ( First_Name VARCHAR(255), Matches INT, Runs INT, AVG FLOAT, Centuries INT, HalfCenturies INT ); sqpte>
Following statement retrieves data combining the values in these two tables −
sqpte> 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 Kohp Indi 239 11520 43 54 Rohit Sharma Indi 218 8686 24 42 sqpte>
Join clause using python
Following SQLite example, demonstrates the JOIN clause using python −
import sqpte3 #Connecting to sqpte conn = sqpte3.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, Krishna@mymail.com , Hyderabad ), ( Vinay , Battacharya , 20, M , 6000.0, 102, 102, Raja@mymail.com , Vishakhapatnam ), ( Sharukh , Sheik , 25, M , 8300.0, 103, 103, Krishna@mymail.com , Pune ), ( Sarmista , Sharma , 26, F , 10000.0, 104, 104, Raja@mymail.com , Mumbai )]Advertisements