The Wayback Machine - https://web.archive.org/web/20241112191643/https://www.geeksforgeeks.org/python-sqlite/
Open In App

Python SQLite

Last Updated : 09 Aug, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

Python SQLite3 module is used to integrate the SQLite database with Python. It is a standardized Python DBI API 2.0 and provides a straightforward and simple-to-use interface for interacting with SQLite databases. There is no need to install this module separately as it comes along with Python after the 2.5x version.

Python SQLite tutorial

This Python SQLite tutorial will help to learn how to use SQLite3 with Python from basics to advance with the help of good and well-explained examples and also contains Exercises for honing your skills.

Introduction

SQLite Queries

Working with Tables

Working with Images

Exercises

Python SQLite – FAQs

Can You Use SQLite with Python?

Yes, you can use SQLite with Python. Python comes with built-in support for SQLite through the sqlite3 module, which allows you to interact with an SQLite database directly from Python code. This makes it an excellent choice for applications that require a lightweight database without the overhead of a full database management system.

How to Use pysqlite?

pysqlite is an external library in Python that provides SQLite database access. It was the original interface to the SQLite relational database management system before it became integrated into Python’s standard library as sqlite3. Since Python 2.5 and above, sqlite3 is included in Python’s standard library, which essentially provides the same functionalities as pysqlite. If you are using Python 2.5 or later, it is recommended to use sqlite3 instead. Here’s a basic example of using sqlite3:

import sqlite3

# Connect to an SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('example.db')

# Create a cursor object using the cursor() method
cursor = conn.cursor()

# Create table
cursor.execute('''CREATE TABLE IF NOT EXISTS stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
conn.commit()

# Close the connection
conn.close()

What is the Purpose of the Python SQLite Connector?

The purpose of the Python SQLite connector, implemented as the sqlite3 module, is to provide a lightweight disk-based database that doesn’t require a separate server process. It allows Python applications to access SQLite databases in a straightforward way. It’s used to execute SQL commands and queries, handle databases, and perform other database management tasks.

What is the Difference Between SQLite and MySQL?

  • SQLite:
  • SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.
  • It is embedded into the end program. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.
  • It is used predominantly for applications that need a lightweight database without the need for a network-accessible database management system.
    • MySQL:
      • MySQL is a full-featured relational database management system (RDBMS) that supports a wide array of features and extensive customization.
      • It operates as a server providing multi-user access to a number of databases.
      • Suitable for large scale applications and websites that need to handle large volumes of data and high user loads.

    Is sqlite3 Part of Python?

    Yes, sqlite3 is part of Python’s standard library for versions 2.5 and later. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249. This means you don’t need to install anything extra if you’re using these versions of Python; you can start using SQLite databases in your applications right away.



    N
    nikhilaggarwal3
    News
    Improve
    Next Article
    Introduction to SQLite in Python

    Similar Reads

    Python SQLite - Insert Data
    In this article, we will discuss how can we insert data in a table in the SQLite database from Python using the sqlite3 module. The SQL INSERT INTO statement of SQL is used to insert a new row in a table. There are two ways of using the INSERT INTO statement for inserting rows: Only values: The first method is to specify only the value of data to b
    3 min read
    SQL using Python and SQLite | Set 2
    Databases offer numerous functionalities by which one can manage large amounts of information easily over the web, and high-volume data input and output over a typical file such as a text file. SQL is a query language and is very popular in databases. Many websites use MySQL. SQLite is a "light" version that works over syntax very much similar to S
    3 min read
    Python SQLite - Update Data
    In this article, we will discuss how we can update data in tables in the SQLite database using Python - sqlite3 module. The UPDATE statement in SQL is used to update the data of an existing table in the database. We can update single columns as well as multiple columns using UPDATE statement as per our requirement. Syntax: UPDATE table_name SET col
    7 min read
    How to Create a Backup of a SQLite Database using Python?
    In this article, we will learn How to Create a Backup of an SQLite Database using Python. To Create a Backup of an SQLite Database using Python, the required modules are SQLite3 and IO. First, let's create the original database to do that follow the below program: C/C++ Code import sqlite3 import io from sqlite3 import Error def SQLite_connection()
    2 min read
    Introduction to SQLite in Python
    Databases offer numerous functionalities by which one can manage large amounts of information easily over the web and high-volume data input and output over a typical file such as a text file. SQL is a query language and is very popular in databases. Many websites use MySQL. SQLite is a "light" version that works over syntax very much similar to SQ
    3 min read
    Python SQLite - Update Specific Column
    In this article, we will discuss how to update a specific column of a table in SQLite using Python. In order to update a particular column in a table in SQL, we use the UPDATE query. The UPDATE statement in SQL is used to update the data of an existing table in the database. We can update single columns as well as multiple columns using UPDATE stat
    3 min read
    How to store Python functions in a Sqlite table?
    SQLite is a relational database system contained in a C library that works over syntax very much similar to SQL. It can be fused with Python with the help of the sqlite3 module. The Python Standard Library sqlite3 was developed by Gerhard Häring. It delivers an SQL interface compliant with the DB-API 2.0 specification described by PEP 249. To use t
    3 min read
    Python SQLite - JOIN Clause
    In this article, we discuss the JOIN clause in SQLite using the sqlite3 module in Python. But at first let's see a brief about join in SQLite. Join Clause A JOIN clause combines the records from two tables on the basis of common attributes. The different types of joins are as follows: INNER JOIN (OR JOIN) - Gives the records that have common attrib
    5 min read
    Creating a sqlite database from CSV with Python
    Prerequisites: PandasSQLite SQLite is a software library that implements a lightweight relational database management system. It does not require a server to operate unlike other RDBMS such as PostgreSQL, MySQL, Oracle, etc. and applications directly interact with a SQLite database. SQLite is often used for small applications, particularly in embed
    2 min read
    Python SQLite - CRUD Operations
    In this article, we will go through the CRUD Operation using the SQLite module in Python. CRUD Operations The abbreviation CRUD expands to Create, Read, Update and Delete. These four are fundamental operations in a database. In the sample database, we will create it, and do some operations. Let's discuss these operations one by one with the help of
    4 min read
    Python SQLite - Create Table
    In this article, we will discuss how can we create tables in the SQLite database from the Python program using the sqlite3 module. In SQLite database we use the following syntax to create a table: CREATE TABLE database_name.table_name( column1 datatype PRIMARY KEY(one or more columns), column2 datatype, column3 datatype, ..... columnN datatype ); N
    1 min read
    SQLite Datatypes and its Corresponding Python Types
    SQLite is a C-language-based library that provides a portable and serverless SQL database engine. It has a file-based architecture; hence it reads and writes to a disk. Since SQLite is a zero-configuration database, no installation or setup is needed before its usage. Starting from Python 2.5.x, SQLite3 comes default with python. In this article, w
    3 min read
    How to connect to SQLite database that resides in the memory using Python ?
    In this article, we will learn how to Connect an SQLite database connection to a database that resides in the memory using Python. But first let brief about what is sqlite. SQLite is a lightweight database software library that provides a relational database management system. Generally, it is a server-less database that can be used within almost a
    3 min read
    How to Delete a Specific Row from SQLite Table using Python ?
    In this article, we will discuss how to delete of a specific row from the SQLite table using Python. In order to delete a particular row from a table in SQL, we use the DELETE query, The DELETE Statement in SQL is used to delete existing records from a table. We can delete a single record or multiple records depending on the condition we specify in
    3 min read
    How to Update all the Values of a Specific Column of SQLite Table using Python ?
    In this article, we are going to update all the values of a specific column of a given SQLite table using Python. In order to update all the columns of a particular table in SQL, we use the UPDATE query. The UPDATE statement in SQL is used to update the data of an existing table in the database. We can update single columns as well as multiple colu
    3 min read
    Python SQLite - ORDER BY Clause
    In this article, we will discuss ORDER BY clause in SQLite using Python. The ORDER BY statement is a SQL statement that is used to sort the data in either ascending or descending according to one or more columns. By default, ORDER BY sorts the data in ascending order. DESC is used to sort the data in descending order.ASC to sort in ascending order.
    3 min read
    Python SQLite - LIMIT Clause
    In this article, we are going to discuss the LIMIT clause in SQLite using Python. But first, let's get a brief about the LIMIT clause. If there are many tuples satisfying the query conditions, it might be resourceful to view only a handful of them at a time. LIMIT keyword is used to limit the data given by the SELECT statement. Syntax: SELECT colum
    2 min read
    Python SQLite - DROP Table
    In this article, we will discuss the DROP command in SQLite using Python. But first, let's get a brief about the drop command. DROP is used to delete the entire database or a table. It deleted both records in the table along with the table structure. Syntax: DROP TABLE TABLE_NAME; For dropping table, we will first create a database and a table in i
    2 min read
    Change SQLite Connection Timeout using Python
    In this article, we will discuss how to change the SQLite connection timeout when connecting from Python. What is a connection timeout and what causes it? A connection timeout is an error that occurs when it takes too long for a server to respond to a user's request. Connection timeouts usually occur when there are multiple active connections to a
    3 min read
    Python SQLite - Deleting Data in Table
    In this article, we will discuss how we can delete data in the table in the SQLite database from the Python program using the sqlite3 module. In SQLite database we use the following syntax to delete data from a table: DELETE FROM table_name [WHERE Clause] To create the database, we will execute the following code: C/C++ Code import sqlite3 # Connec
    2 min read
    How to Count the Number of Rows of a Given SQLite Table using Python?
    In this article, we will discuss how we can count the number of rows of a given SQLite Table using Python. We will be using the cursor_obj.fetchall() method to do the same. This method fetches all the rows of a query result. It returns all the rows as a list of tuples. An empty list is returned if there is no record to fetch. To create the database
    2 min read
    How to Alter a SQLite Table using Python ?
    In this article, we will discuss how can we alter tables in the SQLite database from a Python program using the sqlite3 module. We can do this by using ALTER statement. It allows to: Add one or more column to the tableChange the name of the table.Adding a column to a table The syntax of ALTER TABLE to add a new column in an existing table in SQLite
    3 min read
    How to Show all Columns in the SQLite Database using Python ?
    In this article, we will discuss how we can show all columns of a table in the SQLite database from Python using the sqlite3 module. Approach:Connect to a database using the connect() method.Create a cursor object and use that cursor object created to execute queries in order to create a table and insert values into it.Use the description keyword o
    3 min read
    How to Import a CSV file into a SQLite database Table using Python?
    In this article, we are going to discuss how to import a CSV file content into an SQLite database table using Python. Approach:At first, we import csv module (to work with csv file) and sqlite3 module (to populate the database table).Then we connect to our geeks database using the sqlite3.connect() method.At this point, we create a cursor object to
    3 min read
    Check if Table Exists in SQLite using Python
    In this article, we will discuss how to check if a table exists in an SQLite database using the sqlite3 module of Python. In an SQLite database, the names of all the tables are enlisted in the sqlite_master table. So in order to check if a table exists or not we need to check that if the name of the particular table is in the sqlite_master table or
    2 min read
    Python SQLite - Creating a New Database
    In this article, we will discuss how to create a Database in SQLite using Python. Creating a Database You do not need any special permissions to create a database. The sqlite3 command used to create the database has the following basic syntax Syntax: $ sqlite3 <database_name_with_db_extension> The database name must always be unique in the RD
    3 min read
    How to Execute a Script in SQLite using Python?
    In this article, we are going to see how to execute a script in SQLite using Python. Here we are executing create table and insert records into table scripts through Python. In Python, the sqlite3 module supports SQLite database for storing the data in the database. Approach Step 1: First we need to import the sqlite3 module in Python. import sqlit
    2 min read
    Count total number of changes made after connecting SQLite to Python
    In this article, we are going to see how to count total changes since the SQLite database connection is open using Python. To get the total number of changes we use the connection object's total_changes property. Class Instance: sqlite3.Connection Syntax: <connection_object>.total_changes Return Value: Total no. of rows inserted, deleted, upd
    3 min read
    How to import CSV file in SQLite database using Python ?
    In this article, we'll learn how to import data from a CSV file and store it in a table in the SQLite database using Python. You can download the CSV file from here which contains sample data on the name and age of a few students. Approach: Importing necessary modulesRead data from CSV file DictReader()Establish a connection with the database.sqlit
    2 min read
    Python SQLite - Cursor Object
    In this article, we are going to discuss cursor objects in sqlite3 module of Python. Cursor Object It is an object that is used to make the connection for executing SQL queries. It acts as middleware between SQLite database connection and SQL query. It is created after giving connection to SQLite database. Syntax: cursor_object=connection_object.ex
    2 min read
    Article Tags :
    Practice Tags :
    three90RightbarBannerImg
    We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
    Lightbox