Skip to main content

How to Use Databases (SQLite) in Python


Introduction:
This tutorial is on how to set up SQLite and Python to use databases within your scripts.

Downloading SQLite:
First we need to download SQLite. To do this, go to http://sqlite.org/download.html and download the latest pre-compiled binary release for your operating system - I am using Windows, I am unsure if this tutorial 100% works with other systems.

Once you have downloaded it, use WinRar (You need to download and install this too if you haven't already got it installed) to open it, then drag and drop (extract) the .exe file to a folder.

Creating a Database:
Now open CMD (Command Prompt) on Windows, and navigate to the directory where you have the SQLite.exe file contained. To do this, use "cd" in cmd to Change Directory, mine is...

  1. cd /d G:
  2. cd Libaries\Documents\SQLite

Now we use the command sqlite3 followed by the database name followed by the extension for a database file which is ".db", I will create one named sourcecodester...

  1. sqlite3 sourcecodester.db

This will begin the SQLite service and should create the database. To confirm the database is created, use the command ".databases"...

  1. .databases

If it is there, use ".exit" to exit the service...

  1. .exit

Scripting - Imports:
Now we can create a script to use the database. First we want to import the sqlite(3) package, we use "as db" so we can work with the name db since it is easier...

  1. import sqlite3 as db

Scripting - Connection and Cursor:
The first thing we need to do is connect to the database, we create a variable named "con" equal to the connection. To create the connection we use db.connect followed by the path to our database...

  1. con = db.connect("G:/Libaries/Documents/SQLite/sourcecodester.db")
  2. cursor = con.cursor()

The cursor is what we use to interact with the connection/database.

Scripting - Execute:
We can use the .execute function to use MySQL commands to access the database. We delete the table "test" if it already exists, then we create it with two columns:
col1 with 255 length of text
col2 with 5 length of integer
finally we insert the row of data with col1 as "example" and col2 as 10...

  1. cursor.execute("drop table if exists test")
  2. cursor.execute("create table test(col1 text(255), col2 int(5))")
  3. cursor.execute('insert into test values("example", 10)')
  4. con.commit()
  5. print("Created and inserted.")

Scripting - Commit and Print:
Last but not least, if you are having problems getting the changes to save, use the commit function to finalise any changes made to the database using the execute function. Then we print out a result message...

  1. con.commit()
  2. print("Created and inserted.")

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.