How to Use Databases (SQLite) in Python

Submitted by: 
Language: 
Visitors have accessed this post 370 times.

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.")

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.