python in databases SqLite

In this tutorial you will learn how to use the SQLite database management system with Python. You will learn how to use SQLite, SQL queries, RDBMS and many more things !

Data is everywhere and software applications use that. Data is either in memory, files or databases.





Python has bindings for many database systems including Mysql, Oracle, Microsoft SQL Server and Maria DB.

One of these database management systems (DBMS) is called SQLite.  SQLite was created in the year 2000 and is one of the many management systems in the database zoo.
SQL is a special-purpose programming language designed for managing data held in a database. The language has been around since 1986 and is worth learning. The is an old funny video about SQL

SQLite

SQLite, a relational database management system.

SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

It is a self-contained, serverless, zero-configuration, transactional SQL database engine. The SQLite project is sponsored by Bloomberg and Mozilla.

Install SQLite:

Use this command to install SQLite:

$ sudo apt-get install SQLite

Verify if it is correctly installed. Copy this program and save it as test1.py


#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

con = None

try:
    con = lite.connect('test.db')
    cur = con.cursor()  
    cur.execute('SELECT SQLITE_VERSION()')
    data = cur.fetchone()
    print "SQLite version: %s" % data              
except lite.Error, e: 
    print "Error %s:" % e.args[0]
    sys.exit(1)
finally:  
    if con:
        con.close()

Execute with:

$ python test1.py

It should output:

SQLite version: 3.8.2

What did the script above do?
The script connected to a new database called test.db with this line:

con = lite.connect('test.db')

It then queries the database management system with the command

SELECT SQLITE_VERSION()

which in turn returned its version number. That line is known as an SQL query.

SQL Create and Insert

The script below will store data into a new database called user.db

#!/usr/bin/python # -*- coding: utf-8 -*-   import sqlite3 as lite import sys   con = lite.connect('user.db')   with con:   cur = con.cursor() cur.execute("CREATE TABLE Users(Id INT, Name TEXT)") cur.execute("INSERT INTO Users VALUES(1,'Michelle')") cur.execute("INSERT INTO Users VALUES(2,'Sonya')") cur.execute("INSERT INTO Users VALUES(3,'Greg')") SQLite is a database management system that uses tables. These tables can have relations with other tables: it’s called relational database management system or RDBMS.  The table defines the structure of the data and can hold the data.  A database can hold many different tables. The table gets created using the command: cur.execute("CREATE TABLE Users(Id INT, Name TEXT)") We add  records into the table with these commands: cur.execute("INSERT INTO Users VALUES(2,'Sonya')") cur.execute("INSERT INTO Users VALUES(3,'Greg')") The first value is the ID. The second value is the name.  Once we run the script the data gets inserted into the database table Users: sql table like this !