World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Download our e-book of Introduction To Python
Related Blog
Top Discussion
USA(+256704300651)Top Powerful Love Spells That Really Work By Powerful Revenge Spell Caster in# Canada, #Australia #,UK,//USA Black magic IN >@>USA,/ UK,/ Kuwait,/ Germany, +256704300651--- …Extremely Powerful Death Spells Caster To Kill An Enemy With No Evidence / Ex Husband How can I write Python code to change a date string from "mm/dd/yy hh: mm" format to "YYYY-MM-DD HH: mm" format? +256704300651- Psychic lost love Spell Caster // Divorce Spell // Marriage Proposal A SPELL CASTER/╬ ₩௹]+256704300651₩௹]╬MAMA ZAMIE A TRADITIONAL HEALER /SANGOMA AND A SPIRITUAL HEALER in BELGIUM, +256704300651 == WASHINGTON DC// TOP DEATH SPELLS @POWERFUL REVENGE DEATH SPELL CASTER IN USA,CANADA, UK, CALIFONIA, MEXICO, IRELAND, NY, CANADA, LONDON, SWEDEN, BOTSWANA Exception Type: JSONDecodeError at /update/ Exception Value: Expecting value: line 1 column 1 (char 0) FORTUNER TELLER-(( +256704300651))- Most Effective death revenge Spells Caster USA, UK, BAHAMAS, BAHRAIN, NEW ZEALAND,SPAIN AUSTRALIA SOUTH SUDAN Join Discussion
Top Courses
Master's In Artificial Intelligence Job Guarantee Program
4.5 (1,292 Ratings)
559 Learners
Webinars
Live Masterclass on : "How Machine Get Trained in Machine Learning?"
Jun 1st (5:32 PM) 515 Registered
Python SQLite - Select Data
Mohit Sharma
a year ago
Table of Contents
- Introduction
- Retrieving Data Using Python
Introduction
Today in this article, I will try to give you an idea of how we can use Python and SQLite to manipulate data in the table. I will also give you some example's and show you how we can write the query in python. So let's start...
You can retrieve data from an SQLite table using the SELECT query. This query/statement returns contents of the specified relation (table) in tabular form and it is called as result-set.
Syntax
Following is the syntax of the SELECT statement in SQLite −
SELECT column1, column2, columnN FROM table_name;
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 into 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 query retrieves the values of the columns FIRST_NAME, LAST_NAME and, COUNTRY from the CRICKETERS table.
sqlite> SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS;
Shikhar |Dhawan |India
Jonathan |Trott |SouthAfrica
Kumara |Sangakkara |Srilanka
Virat |Kohli |India
Rohit |Sharma |India
sqlite>
As you observe, the SELECT statement of the SQLite database just returns the records of the specified tables. To get a formatted output you need to set the header, and mode using the respective commands before the SELECT statement as shown below −
sqlite> .header on
sqlite> .mode column
sqlite> SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS;
First_Name Last_Name Country
---------- ---------- ----------
Shikhar Dhawan India
Jonathan Trott SouthAfric
Kumara Sangakkara rilanka
Virat Kohli India
Rohit Sharma India
If you want to retrieve all the columns of each record, you need to replace the names of the columns with "*" as shown below −
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM CRICKETERS;
First_Name Last_Name Age Place_Of_Birth Country
---------- ---------- ------- -------------- ----------
Shikhar Dhawan 33 Delhi India
Jonathan Trott 38 CapeTown SouthAfric
Kumara Sangakkara 41 Matale Srilanka
Virat Kohli 30 Delhi India
Rohit Sharma 32 Nagpur India
sqlite>
In SQLite by default, the width of the columns is 10 values beyond this width are chopped (observe the country column of 2nd row in the above table). You can set the width of each column to required value using the .width command, before retrieving the contents of a table as shown below −
sqlite> .width 10, 10, 4, 10, 13
sqlite> SELECT * FROM CRICKETERS;
First_Name Last_Name Age Place_Of_B 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
sqlite>
Retrieving Data Using Python
READ Operation on any database means to fetch some useful information from the database. You can fetch data from MYSQL using the fetch() method provided by the SQLite Python module.
The sqlite3.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 a 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
Following example fetches all the rows of the EMPLOYEE table using the SELECT query and from the obtained result set initially, we are retrieving the first row using the fetchone() method and then fetching the remaining rows using the fetchall() method.
The following python program shows how to fetch and display records from the COMPANY table created in the above example.
import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')
#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)
]
To learn more about python, visit the InsideAIML page.
I hope you enjoyed reading this article and finally, you came
to know about Python SQLite - Select Data
For more such blogs/courses on data science, machine
learning, artificial intelligence and emerging new technologies do visit us at InsideAIML.
Thanks for reading…
Happy Learning…