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

For clarification, and as seen in the reference tutorial. "Python gained the sqlite3 module all the way back in version 2.5 which means that you can create SQLite database with any current Python without downloading any additional dependencies." The documentation is available at https://docs.python.org/2/library/sqlite3.html

I was searching this question, since I was completing the 'install_requires' section of my setup.py and wanted to make sure sqlite3 was included.

I noticed on Macports, I needed to explicitly install sqlite3 and py27-sqlite.

A simple way to test your version.

>>> import sqlite3
>>> sqlite3.version
'2.6.0'

or whatever version installed will show up, or you will get an ImportError if it is not installed.

With some quick searches of pysqlite you can find some options for older versions of sqlite.

Answer 2

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 3

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.

Answer 4

No, you would not need to install any other module but python-sqlite.

Run sudo apt-get install python-sqlite on Ubuntu

Also, when you see in the above code see one database named mydatabase.db. This is the name of the file that shall be used to store your data. Sqlite stores data in the form of flat files only. So this file ("mydatabase.db" in this case) will contain your database schema and content.

As no absoulute path given this will be created in the present working directory, that is, the directory from script is running.