Creating a Database using SQLite

Creating a Database using SQLite

The following example 15.3 explains how a connection to be made to a database through Python sqlite3

#Python code to demonstrate table creation and insertions with SQL

#importing module

import sqlite3

# connecting to the database

connection = sqlite3.connect ("Academy.db")

# cursor

cursor = connection.cursor()

In the above example a database with the name "Academy" would be created. It's similar to the sql command "CREATE DATABASE Academy;" to SQL server."sqlite3.connect ('Academy.db')" is again used in some program, "connect" command just opens the already created database.

Creating a Table

After having created an empty database, you will most probably add one or more tables to this database. The SQL syntax for creating a table "Student" in the database "Academy" looks like as follows :

CREATE TABLE Student (

RollnoINTEGER, SnameVARCHAR(20), GradeCHAR(1), gender CHAR(1),

Average float(5.2), birth_date DATE, PRIMARY KEY (Rollno) );

This is the way, somebody might do it on a SQL command shell. Of course, we want to do this directly from Python. To be capable to send a command to "SQL", or SQLite, we need a cursor object. Usually, a cursor in SQL and databases is a control structure to traverse over the records in a database. So it's used for the fetching of the results.

The cursor object is created by calling the cursor() method of connection. The cursor is used to traverse the records from the result set. You can define a SQL command with a triple quoted string in Python. The reason behind the triple quotes is sometime the values in the table might contain single or double quotes.

Example 15.3.1

sql_command = """

CREATE TABLE Student (

Rollno INTEGER PRIMARY KEY ,

Sname VARCHAR(20),

Grade CHAR(1),

gender CHAR(1),

Average DECIMAL(5,2),

birth_date DATE);"""

In the above example the Emp_no field as "INTEGER PRIMARY KEY" A column which is labeled like this will be automatically auto-incremented in SQLite3. To put it in other words: If a column of a table is declared to be an INTEGER PRIMARY KEY, then whenever a NULL will be used as an input for this column, the NULL will be automatically converted into an integer which will one larger than the highest value so far used in that column. If

the table is empty, the value 1 will be used.

Adding Records

To populate (add record) the table "INSERT" command is passed to SQLite. “execute” method executes the SQL command to perform some action. The following example 15.3.2 is a complete working example. To run the program you will either have to remove the file Academy. db or uncomment the "DROP TABLE" line in the SQL command:

Example

import sqlite3

connection = sqlite3.connect ("Academy.db")

cursor = connection.cursor()

# delete

cursor.execute ("""DROP TABLE Student;""")

sql_command = """

CREATE TABLE Student (

Rollno INTEGER PRIMARY KEY , Sname VARCHAR(20), Grade CHAR(1), gender CHAR(1), Average DECIMAL (5, 2), birth_date DATE);""" cursor.execute(sql_command)

sql_command = """INSERT INTO Student (Rollno, Sname, Grade, gender, Average, birth_date)

VALUES (NULL, "Akshay", "B", "M","87.8", "2001-12-12");""" cursor.execute(sql_ command)

sql_command = """INSERT INTO Student (Rollno, Sname, Grade, gender, Average, birth_date)

VALUES (NULL, "Aravind", "A", "M","92.50","2000-08-17");""" cursor.execute(sql_ command)

#never forget this, if you want the changes to be saved: connection.commit()

connection.close()

print("STUDENT TABLE CREATED")

OUTPUT

STUDENT TABLE CREATED

Of course, in most cases, you will not literally insert data into a SQL table. You will rather have a lot of data inside of some Python data type e.g. a dictionary or a list, which has to be used as the input of the insert statement.

The following working example, assumes that you have an already existing database Academy.db and a table Student. We have a list with data of persons which will be used in the INSERT statement:

Example

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

student_data = [("BASKAR", "C", "M","75.2","1998-05-17"), ("SAJINI", "A", "F","95.6","2002-11-01"), ("VARUN", "B", "M","80.6","2001-03-14"), ("PRIYA", "A", "F","98.6","2002-01-01"), ("TARUN", "D", "M","62.3","1999-02-01") ]

for p in student_data:

format_str = """INSERT INTO Student (Rollno, Sname, Grade, gender,Average, birth_date)

VALUES (NULL,"{name}", "{gr}", "{gender}","{avg}","{birthdate}");"""

sql_command = format_str.format(name=p[0], gr=p[1], gender=p[2],avg=p[3], birthdate = p[4])

cursor.execute(sql_command)

connection.commit()

connection.close()

print("RECORDS ADDED TO STUDENT TABLE ")

OUTPUT

RECORDS ADDED TO STUDENT TABLE