How to work with sqlite3 and Python

Question

I was looking for any database solution with Python. And found the tutorial Python: A Simple Step-by-Step SQLite Tutorial. There I found a code example which shows how to create a database and INSERT some data:

import sqlite3

conn = sqlite3.connect("mydatabase.db") # or use :memory: to put it in RAM

cursor = conn.cursor()

# create a table
cursor.execute("""CREATE TABLE albums
                  (title text, artist text, release_date text, 
                   publisher text, media_type text) 
               """)

I am totally new to sqlite3.

• If I want to use sqlite3 do I need to install any particular Python modules?
• In the above code I can see one database named mydatabase.db. How do I create that database?

If anyone help me to get these confusions cleared from my head, I can give these new module a good start.

Thanks

Answer 1

You don't need (to install) any additional Python modules to use sqlite3.

If the database doesn't exist, it will be automatically created usually in the same directory as the script.

On running your script, I get this :-

$ ls *.db
ls: *.db: No such file or directory

$ python test.py

$ ls *.db
mydatabase.db

$ sqlite3 mydatabase.db 
SQLite version 3.7.7 2011-06-25 16:35:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select * from sqlite_master;
table|albums|albums|2|CREATE TABLE albums
             (title text, artist text, release_date text, 
              publisher text, media_type text)
sqlite> 

Answer 2

Sqlite3 is the version commonly used with Python. If you are running Windows, you need to download sqlite download from official page. If you are using Ubuntu or Debian based system then it comes pre-installed.

Now coming to python, sqlite3 is the package name, it comes included with python, if you don't find it, then install it with the command apt-get install python-sqlite on Ubuntu system.

Considering you are using Ubuntu system, simply type sqlite3 test.db to create database name test.db.

As for your program:

import sqlite3 

conn = sqlite3.connect("test.db") # connect to test.db 
cursor = conn.cursor() # get a cursor to the sqlite database 
# cursor is the object here, you can use any name

# create a table
cursor.execute("""CREATE TABLE albums
                  (title text, artist text, release_date text, 
                   publisher text, media_type text)""")
# cursor.execute is used to execute any command to sqlite

Few more function I would like to introduce is data = cursor.fetchone() to fetch one row, data = cursor.fetchall() to fetch many rows at once and store in tuple data.