How to Use SQLite with Python

This tutorial is for those who want to learn how to use sqlite with python programming language. Depending on your operating system, you need to have python setup on your computer. I assume you have working python instillation on your operating system. If not then head over to python.org and download the python distribution respective to your operating system version. Ubuntu Linux users can read this tutorial for setting up python under ubuntu. Windows users can follow this tutorial – python on windows.

For ironpython users, there is already one tutorial on this site, check Sqlite with Ironpyton.

Now that we have all things that we need to run python, let’s start with the code.

Check Python

In order to test your installation of python under windows operating system. Open command prompt and type:

Python

This command will invoke python interpreter inside the command prompt irrespective of the directory. If it’s throwing error then you’ve broken setup of python. You need to setup environment variables and add entry to python directory in PATH.

Check Sqlite3

SQLite3 is the latest version of sqlite as of now. You can check it’s pysqlite binding version by typing the following command.

sqlite3.version

This command will show you the pysqlite version of sqlite3.

Check Sqlite Database Library Version

Type the following command –

sqlite3.sqlite_version

This command gives us sqlite database library version.

These are some introductory commands that you’re going to run for now. You don’t need to run them later as they’re not important. But you need to know these commands to find out version of sqlite and it’s database library if you’re reporting errors to developer or building your own python packages.

SQLite Commands

When you work with sqlite there are some dot commands that you need to know to do some tasks effectively. Take a look at some of these commands.

• .help – Shows Help for Sqlite
• .databases – Dump a table into an SQL text format
• .exit – Exit Sqlite
• .show – Show sqlite settings
• .read filename – Execute SQL Commands in Filename
• .mode mode table – Changes mode for a table
• .header ON|OFF – Show or Hide Column headers
• .tables pattern – list tables that match pattern
• .width num num – set width for columns
• .quit – Quits SQLite program

We’re done with introductory part of sqlite and it’s helpful commands. So let’s dive into database programming with python code.

Creating Database

In order to create database you need to import sqlite in your first command and then you need to create the database by assigning it a new name.

import sqlite3
import sys
con=sqlite3.connect(“name_of_db.db”)

In first line we imported sqlite3 library and in second command we imported systems library. Third line of our code is for creating sqlite3 database.

You can check the status of our newly created database with the dot command.

.databases

You’ll find the new database listed by this command. If you want to check the database file, you can browse to the directory from which you’re running the command and see the file.

Creating a Table

Following command creates a table inside our current working database.

con.execute('CREATE TABLE tablename(no INTEGER PRIMARY KEY, value VARCHAR(10))')

Here we’re creating the table ‘tablename’ with fields no and value.

Inserting Data

In order to insert data into our columns we have to call the insert command.

con.execute('INSERT INTO tablename VALUES (1,"yeah")')

You can add multiple values if you want and this will be added to the table. You can check the table and the values in the columns by executing .table command.

Commit Data

It is good practice to commit your data. Use commit() in the following format.

con.commit();

Showing Data

You have to show the data to your user as program completes and in such case you can use SELECT command.

con.execute(‘SELECT*FROM tablename’)

Make sure you enter the name of your table correctly.

Drop Table

If you want to drop the table, you have to use command DROP TABLE followed by name of your table.

con.execute(‘DROP TABLE tablename;’)

You can verify if the table is deleted or not by executing the .table command.

So now you know how to create database, add table and data into the table. You also learned about removing table and committing changes to your table. This is just an introduction to the SQLite under python environment. You can explore more by checking every sqlite3 command in the python prompt.

Check out the Video Tutorial on How to Use Python with SQLite Database.

 

I hope this tutorial helps to those who wants to work with python and sqlite3. Feel free to let me know your suggestions and feedback in the comment form below. If the comment form is closed after 180 days you can send me tweet @maheshkale.