Python sqlite select all

Python Select from SQLite Table

This lesson demonstrates how to execute SQLite SELECT Query from Python to retrieve rows from the SQLite table using the built-in module sqlite3.

Goals of this lesson

• Fetch all rows using a cursor.fetchall()
• Use cursor.fetchmany(size) to fetch limited rows, and fetch only a single row using cursor.fetchone()
• Use the Python variables in the SQLite Select query to pass dynamic values.

Table of contents

Prerequisite

Before executing the following program, please make sure you know the SQLite table name and its column details.

For this lesson, I am using the ‘SqliteDb_developers’ table present in my SQLite database.

If a table is not present in your SQLite database, then please refer to the following articles: –

Steps to select rows from SQLite table

How to Select from a SQLite table using Python

1. Connect to SQLite from Python Refer to Python SQLite database connection to connect to SQLite database.
2. Define a SQLite SELECT Query Next, prepare a SQLite SELECT query to fetch rows from a table. You can select all or limited rows based on your requirement.
   For example, SELECT column1, column2, columnN FROM table_name;
3. Get Cursor Object from Connection Next, use a connection.cursor() method to create a cursor object. This method returns a cursor object. The Cursor object is required to execute the query.
4. Execute the SELECT query Execute the select query using the cursor.execute(query) method.
5. Extract all rows from a result After successfully executing a select operation, Use the fetchall() method of a cursor object to get all rows from a query result. it returns a list of rows.
6. Iterate each row Iterate a row list using a for loop and access each row individually (Access each row’s column data using a column name or index number.)
7. Close the cursor object and database connection object use cursor.clsoe() and connection.clsoe() method to close the SQLite connection after your work completes.

Example to read all rows from SQLite table

import sqlite3 def readSqliteTable(): try: sqliteConnection = sqlite3.connect('SQLite_Python.db') cursor = sqliteConnection.cursor() print("Connected to SQLite") sqlite_select_query = """SELECT * from SqliteDb_developers""" cursor.execute(sqlite_select_query) records = cursor.fetchall() print("Total rows are: ", len(records)) print("Printing each row") for row in records: print("Id: ", row[0]) print("Name: ", row[1]) print("Email: ", row[2]) print("JoiningDate: ", row[3]) print("Salary: ", row[4]) print("\n") cursor.close() except sqlite3.Error as error: print("Failed to read data from sqlite table", error) finally: if sqliteConnection: sqliteConnection.close() print("The SQLite connection is closed") readSqliteTable()

Connected to SQLite Total rows are: 6 Printing each row Id: 1 Name: James Email: james@pynative.com JoiningDate: 2019-03-17 Salary: 8000.0 Id: 2 Name: Joe Email: joe@pynative.com JoiningDate: 2019-05-19 Salary: 9000.0 Id: 3 Name: Ben Email: ben@pynative.com JoiningDate: 2019-02-23 Salary: 9500.0 Id: 4 Name: Jos Email: jos@gmail.com JoiningDate: 2019-01-14 Salary: 9500.0 Id: 5 Name: Chris Email: chris@gmail.com JoiningDate: 2019-05-15 Salary: 7600.0 Id: 6 Name: Jonny Email: jonny@gmail.com JoiningDate: 2019-03-27 Salary: 8400.0 The SQLite connection is closed

Note: I am directly displaying each row and its column values. If you want to use column values in your program, you can copy them into python variables to use it. For example, name = row[1]

Use Python variables as parameters in SQLite Select Query

We often need to pass a variable to SQLite select query in where clause to check some condition.

Let’s say the application wants to fetch person details by giving any id at runtime. To handle such a requirement, we need to use a parameterized query.

A parameterized query is a query in which placeholders ( ? ) are used for parameters and the parameter values supplied at execution time.

cursor.execute("SELECT salary FROM SqliteDb_developers WHERE )

import sqlite3 def getDeveloperInfo(id): try: sqliteConnection = sqlite3.connect('SQLite_Python.db') cursor = sqliteConnection.cursor() print("Connected to SQLite") sql_select_query = """select * from SqliteDb_developers where cursor.execute(sql_select_query, (id,)) records = cursor.fetchall() print("Printing ID ", id) for row in records: print("Name = ", row[1]) print("Email = ", row[2]) print("JoiningDate = ", row[3]) print("Salary = ", row[4]) cursor.close() except sqlite3.Error as error: print("Failed to read data from sqlite table", error) finally: if sqliteConnection: sqliteConnection.close() print("The SQLite connection is closed") getDeveloperInfo(2) 

Connected to SQLite Printing ID 2 Name = Joe Email = joe@pynative.com JoiningDate = 2019-05-19 Salary = 9000.0 The SQLite connection is closed

Select limited rows from SQLite table using cursor.fetchmany()

In some circumstances, fetching all the data rows from a table is a time-consuming task if a table contains thousands of rows.

To fetch all rows, we have to use more resources, so we need more space and processing time. To enhance performance, use the fetchmany(SIZE) method of a cursor class to fetch fewer rows.

import sqlite3 def readLimitedRows(rowSize): try: sqliteConnection = sqlite3.connect('SQLite_Python.db') cursor = sqliteConnection.cursor() print("Connected to SQLite") sqlite_select_query = """SELECT * from SqliteDb_developers""" cursor.execute(sqlite_select_query) print("Reading ", rowSize, " rows") records = cursor.fetchmany(rowSize) print("Printing each row \n") for row in records: print("Id: ", row[0]) print("Name: ", row[1]) print("Email: ", row[2]) print("JoiningDate: ", row[3]) print("Salary: ", row[4]) print("\n") cursor.close() except sqlite3.Error as error: print("Failed to read data from sqlite table", error) finally: if sqliteConnection: sqliteConnection.close() print("The SQLite connection is closed") readLimitedRows(2)

Connected to SQLite Reading 2 rows Printing each row Id: 1 Name: James Email: james@pynative.com JoiningDate: 2019-03-17 Salary: 8000.0 Id: 2 Name: Joe Email: joe@pynative.com JoiningDate: 2019-05-19 Salary: 9000.0 The SQLite connection is closed

Note: In the above program, the specified size is 2 to fetch two records. If the SQLite table contains rows lesser than the specified size, then fewer rows will return.

Select a single row from SQLite table

When you want to read only one row from the SQLite table, then you should use fetchone() method of a cursor class. You can also use this method in situations when you know the query is going to return only one row.

The cursor.fetchone() method retrieves the next row from the result set.

import sqlite3 def readSingleRow(developerId): try: sqliteConnection = sqlite3.connect('SQLite_Python.db') cursor = sqliteConnection.cursor() print("Connected to SQLite") sqlite_select_query = """SELECT * from SqliteDb_developers where cursor.execute(sqlite_select_query, (developerId,)) print("Reading single row \n") record = cursor.fetchone() print("Id: ", record[0]) print("Name: ", record[1]) print("Email: ", record[2]) print("JoiningDate: ", record[3]) print("Salary: ", record[4]) cursor.close() except sqlite3.Error as error: print("Failed to read single row from sqlite table", error) finally: if sqliteConnection: sqliteConnection.close() print("The SQLite connection is closed") readSingleRow(3) 

Connected to SQLite Reading single row Id: 3 Name: Ben Email: ben@pynative.com JoiningDate: 2019-02-23 Salary: 9500.0 The SQLite connection is closed

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

About Vishal

I’m Vishal Hule, Founder of PYnative.com. I am a Python developer, and I love to write articles to help students, developers, and learners. Follow me on Twitter

Related Tutorial Topics:

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

• 15+ Topic-specific Exercises and Quizzes
• Each Exercise contains 10 questions
• Each Quiz contains 12-15 MCQ

Источник

SQLite Python: Querying Data

Summary: in this tutorial, we will show you step by step how to query data in SQLite from Python.

To query data in an SQLite database from Python, you use these steps:

1. First, establish a connection to the SQLite database by creating a Connection object.
2. Next, create a Cursor object using the cursor method of the Connection object.
3. Then, execute a SELECT statement.
4. After that, call the fetchall() method of the cursor object to fetch the data.
5. Finally, loop the cursor and process each row individually.

In the following example, we will use the tasks table created in the creating tables tutorial.

First, create a connection to an SQLite database specified by a file:

def create_connection(db_file): """ create a database connection to the SQLite database specified by the db_file :param db_file: database file :return: Connection object or None """ conn = None try: conn = sqlite3.connect(db_file) except Error as e: print(e) return connCode language: Python (python)

This function selects all rows from the tasks table and displays the data:

def select_all_tasks(conn): """ Query all rows in the tasks table :param conn: the Connection object :return: """ cur = conn.cursor() cur.execute("SELECT * FROM tasks") rows = cur.fetchall() for row in rows: print(row)Code language: Python (python)

In the select_all_tasks() function, we created a cursor, executed the SELECT statement, and called the fetchall() to fetch all tasks from the tasks table.

This function query tasks by priority:

def select_task_by_priority(conn, priority): """ Query tasks by priority :param conn: the Connection object :param priority: :return: """ cur = conn.cursor() cur.execute("SELECT * FROM tasks WHERE priority=?", (priority,)) rows = cur.fetchall() for row in rows: print(row)Code language: Python (python)

In the select_task_by_priority() function, we selected the tasks based on a particular priority. The question mark ( ? ) in the query is the placeholder. When the cursor executed the SELECT statement, it substituted the question mark ( ? ) by the priority argument. The fetchall() method fetched all matching tasks by the priority.

This main() function creates a connection to the database C:\sqlite\db\pythonsqlite.db and calls the functions to query all rows from the tasks table and select tasks with priority 1:

def main(): database = r"C:\sqlite\db\pythonsqlite.db" # create a database connection conn = create_connection(database) with conn: print("1. Query task by priority:") select_task_by_priority(conn, 1) print("2. Query all tasks") select_all_tasks(conn)Code language: Python (python)

import sqlite3 from sqlite3 import Error def create_connection(db_file): """ create a database connection to the SQLite database specified by the db_file :param db_file: database file :return: Connection object or None """ conn = None try: conn = sqlite3.connect(db_file) except Error as e: print(e) return conn def select_all_tasks(conn): """ Query all rows in the tasks table :param conn: the Connection object :return: """ cur = conn.cursor() cur.execute("SELECT * FROM tasks") rows = cur.fetchall() for row in rows: print(row) def select_task_by_priority(conn, priority): """ Query tasks by priority :param conn: the Connection object :param priority: :return: """ cur = conn.cursor() cur.execute("SELECT * FROM tasks WHERE priority=?", (priority,)) rows = cur.fetchall() for row in rows: print(row) def main(): database = r"C:\sqlite\db\pythonsqlite.db" # create a database connection conn = create_connection(database) with conn: print("1. Query task by priority:") select_task_by_priority(conn, 1) print("2. Query all tasks") select_all_tasks(conn) if __name__ == '__main__': main()Code language: Python (python)

In this tutorial, you have learned how to develop a Python program to query data from tables in an SQLite database.

Источник