Python SQLite Database Tutorial

---

     SQLite is a light weight in-memory Relational Database. SQLite operates on two modes
1.in-Memory
2. on Local file.
In-Memory means all Database actions stored in Computers' RAM memory. non-persistent, once application shutdowned, all data will be erased
Local File means all Database actions stored in Local File System, Persistent,all data will be stored in the file system.

SQLite mainly used for demo purpose, later this can be ported to higher-end databases like MySQL,DB2,Oracle etc.,

SQLite supports various data types.

• INTEGER

     INTEGER represnts a value with out fractional part, i.e whole number, for ex: 99999, 10000001

• REAL

     REAL represnts a value with fractional part, for ex: 3000.99

• TEXT

     TEXT , any text data

• BLOB

     Binary Large Object(BLOB), document files, media files can be stored in this field

SQLite DataBase supports Dynamic typing, means Type may/ may not be specified at column defintion

Import SQLite Module

      		import sqlite3
      

Objects Used to Connect to SQLite Database

     sqlite3 module has connect and cursor methods,These are two important methods, helps us to connect to SQLite Database, Python Programmer can use these methods to do DDL operations or DML Operations on the Database .

• Connect method

     Connect method accepts Database Name,Isolation Levels, etc.,

connect method returns Cursor object

• Cursor Object

     Cursor Object represents Database Cursor, Which is used to execute SQL statements and maintain context of a search operation. In Other Words , Which is a in-memory representation of SQL Data as a Python Object. and has various methods to fetch Record,Insert record into Database, Update Record and Delete Record.

Cursor object has execute and fetchall methods, Execute method executes SQL statement on SQLite Database,if SQL statement is SELECT statement fetches all records as a list object, each item in the tuple. each value in the tuple reprenets column value.

Cursor object is an iteratable Object, User can iterate over the cursor to fetch resulting rows.

Connect to SQLite Database

     sqlite3 has method called connect , for basic connection, database file should be provided, following ways user can supply database file

• specify Location of the file,using absolute or relative path
• using ":memory:"
• specify file location as URI(Uniform resource Identifier)

		import sqlite3
		
		sqlite3.connect("filepath") //DDL commands stored in a file
		
		sqlite3.connect(":memory:") ////DDL commands stored in a RAM
		
		sqlite3.connect("file:///opt/sqlitetutorial/location.db") //DDL commands stored in a file
	

Create a Table

     Creating a Table using CREATE TABLE DDL command.

in SQLite command Prompt

		sqlite> Create Table Employee(
					empID INTEGER PRIMARY KEY, 
					name varchar(20), 
					job Text, 
					salary REAL,
					DOJ TEXT default CURRENT_DATE NOT NULL;)
	

in Python

		import sqlite3
		
		//Step 1: Connect to Database, get Connection Object.
		
		conn = sqlite3.connect("/home/sitapathipurru/sample.db")
		
		//Step 2: Using Connection Object, use execute method to Execute DDL command.
		
		conn.execute("""
			 Create Table Employee(
					empID INTEGER PRIMARY KEY, 
					name varchar(20), 
					job Text, 
					salary REAL,
					DOJ TEXT default CURRENT_DATE NOT NULL)
			        """)
	

Database table Employee created in SQLite Database file called "sample.db". now launch sqlite at command prompt. Type .tables displays list of tables in sample.db . .schema displays full schema of table Employee, as shown below.

		sqlite3 sample.db
		sqlite>.tables
		Employee
		//Display schema of Employee
		sqlite>.schema
		CREATE TABLE Employee(empID INTEGER, name varchar(20), job Text, salary REAL,DOJ TEXT default CURRENT_DATE NOT NULL);
	

INSERT Records

     SQLite allows Inserting Records into a table using "INSERT" DML command. and also provides another option i.e User can Insert Single record or Multiple Records at a Time.

In Python, connection object has method called "execute", Which submits any SQL command to SQLite Database. Insertion is success returns Nothing else Exception raised sqlite3.IntegrityError: UNIQUE constraint failed: Employee.empID

Inserting Single Record

		import sqlite3
		
		conn  =  sqlite3.connect("/home/sitapathipurru/sample.db")
		
		conn.execute("""
		INSERT INTO Employee(empID,name,job,salary) values(1000,'sam','MANAGER',5500.00)
		""")
	

Inserting Multiple Records using execute method or executemany method.

		import sqlite3
		
		conn  =  sqlite3.connect("/home/sitapathipurru/sample.db")
		
		conn.execute("""
		INSERT INTO Employee(empID,name,job,salary) values
						(1002,'Daniel','Sr.Analyst',3000.00),
						(1004,'Swathi','Analyst',3000.00),
						(1006,'Peter','Sr.Manager',7500.00),
		""")
	

FETCH Records

     Connection Object has Execute method, which returns cursor , a cursor is a Database cursor, its memory area where SQL data stored in Python's Program memory, which has context to scroll records one by one.

Programmer can use for-each loop to iterate all records or use fetchall() method returns list of records, each record is a tuple, which represents database record.

		import sqlite3
		
		conn  =  sqlite3.connect("/home/sitapathipurru/sample.db")
		
		cur = conn.execute("SELECT * FROM EMPLOYEE");
		
		for row in cur:
		 print(row)
	

Using fetchall method

		import sqlite3
		
		conn  =  sqlite3.connect("/home/sitapathipurru/sample.db")
		
		cur = conn.execute("SELECT * FROM EMPLOYEE");
		
		print(cur.fetchall())	
	
	

UPDATE Records

     UPDATE DML command can update zero or more records at time.

		import sqlite3
		
		conn  =  sqlite3.connect("/home/sitapathipurru/sample.db")
		
		cur = conn.execute("UPDATE EMPLOYEE SET SALARY = SALARY+1000 WHERE EMPID=1002");
		
	
	

DELETE Records

    DELETE DML command deletes zero or more commands in SQLite Database. DELETE without condition deletes all records from the table, DELETE with condition, based on condition selected records will be deleted.

		import sqlite3
		
		conn  =  sqlite3.connect("/home/sitapathipurru/sample.db")
		
		cur = conn.execute("DELETE FROM EMPLOYEE WHERE EMPID=1002");
		
	
	

sqlite3.OperationalError: table abc already exists sqlite3.OperationalError: no such table: abc sqlite3.OperationalError: unable to open database file