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.
In order to test your installation of python under windows operating system. Open command prompt and type:
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.
SQLite3 is the latest version of sqlite as of now. You can check it’s pysqlite binding version by typing the following command.
This command will show you the pysqlite version of sqlite3.
Check Sqlite Database Library Version
Type the following command –
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.
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.
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.
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.
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.
It is good practice to commit your data. Use commit() in the following format.
You have to show the data to your user as program completes and in such case you can use SELECT command.
Make sure you enter the name of your table correctly.
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.