What is SQLite3

SQLite3

SQLite3 is a very easy to use database engine. It is self-contained, serverless, zero-configuration and transactional. It is very fast and lightweight, and the entire database is stored in a single disk file. It is used in a lot of applications as internal data storage. The Python Standard Library includes a module called "sqlite3" intended for working with this database. This module is a SQL interface compliant with the DB-API 2.0 specification.

Using Python's SQLite Module

To use the SQLite3 module we need to add an import statement to our python script:

import sqlite3

Connecting SQLite to the Database

We use the function sqlite3.connect to connect to the database. We can use the argument ":memory:" to create a temporary DB in the RAM or pass the name of a file to open or create it.

# Create a database in RAM db = sqlite3.connect(':memory:') # Creates or opens a file called mydb with a SQLite3 DB db = sqlite3.connect('data/mydb')

When we are done working with the DB we need to close the connection:

	db.close()

Creating (CREATE) and Deleting (DROP) Tables

In order to make any operation with the database we need to get a cursor object and pass the SQL statements to the cursor object to execute them. Finally it is necessary to commit the changes. We are going to create a users table with name, phone, email and password columns.

# Get a cursor object cursor = db.cursor() cursor.execute('''     CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT,                        phone TEXT, email TEXT unique, password TEXT) ''') db.commit()

To drop a table:

# Get a cursor object cursor = db.cursor() cursor.execute('''DROP TABLE users''') db.commit()