How to Use Databases (SQLite) in Python

Submitted by Yorkiebar on Sunday, March 16, 2014 - 14:18.
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.")

Tags

Add new comment

About text formats
  • 78 views