How to create a sqlite table then populate data within python 3.5.2

In this python 3.5.2 tutorial we will first create a sqlite table and then populate that table with few rows of data. In order to create a sqlite table we need to import the sqlite3 module into our python 3.5.2 program. The good thing about this module is that it has already been included in python 3.5.2 so we do not need to download it separately into our python package.

Alright, without wasting any time let get started. I am using Eclipse to create my python project, let us create a new PyDev project in Eclipse. If you have read my previous tutorial before then you should know that we need to install the PyDev plugin in order for us to create and run our python project in Eclipse, so if you have not yet installed the plugin just go ahead and do so.

After you have installed the PyDev plugin then go ahead and follow the following instruction to create and populate the sqlite table with data.

1) Before you create this new project you will need to create the MyClassDemo project first because we will need to use it’s PersonalData class in this tutorial. Go ahead and create the MyClassDemo project through this tutorial link :
http://gamingdirectional.com/blog/2016/07/31/how-to-create-a-python-class/

2) After you have created the MyClassDemo project you can now create a new SqliteDemo project in PyDev through File->New->PyDev Project.

Fill in the project detail
Fill in the project detail
check the MyClassDemo project
Check the MyClassDemo project, you will need it to import the personaldata.py module into the SqliteDemo project

3) Next create a new package under the SqliteDemo project’s src folder through right click on the project folder->New->PyDev package.

Fill in the package name then click Finish
Fill in the package name then click Finish

3) Then create the insertEmployeeData.py module under the SqliteDemo package through right click on the package->New->PyDev Module

Fill in module name then click Finish
Fill in the module name then click Finish
Select Module Main
Select Module Main

4) Now your project should have the following file structure.

Project File Structure
Project File Structure

5) Next double click on the insertEmployeeData.py module to open it up on the code editor window.

6) In the editor window, enter the below script to import the PersonalData class from the MyClassDemo project into the insertEmployeeData.py module.

from os import path
import sys
sys.path.append(path.abspath('../MyClassDemo')) #let Eclipse knows where to find the MyClassDemo project path

from MyClass.personaldata import PersonalData 

7) Continue entering the below script under if __name__ == ‘__main__’: to gather the data of that particular employee and then create a new employee object from the PersonalData class and populate it with name, job and salary of that particular employee.

if __name__ == '__main__':
    
    name = input("Enter Employee Name")
    job = input("Enter Employee Job")
    salary = input("Enter Employee Salary")
    
    employeeObject = PersonalData(name, job, salary)

8) Next at the top of the insertEmployeeData.py module import the sqlite3 module into our program as well as provide a new name for our new sqlite3 database.

import sqlite3
db_name = 'employeedata.sqlite'

9) Now create our employee table in the employeedata.sqlite database

# connect to the employeedata.sqlite database, if the database is not there yet then create it
connection = sqlite3.connect('employeedata.sqlite')
cursor = connection.cursor()

# retrieve the employee table name which we have created
cursor.execute("""SELECT name FROM sqlite_sequence WHERE name='employee'""")
name_is_there = cursor.fetchone()[0]


# if the employee table has not been created yet then we shall create it
if name_is_there != 'employee':
    cursor.execute("""CREATE TABLE employee (
                    id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                    name TEXT NOT NULL,
                    job  TEXT NOT NULL,
                    salary TEXT NOT NULL) """)

10) Insert the data we have gathered at the beginning of the program into the employee table.

cursor.execute("INSERT INTO employee (name, job, salary) VALUES (?, ?, ?)", (employeeObject.name, employeeObject.work, employeeObject.earning)) # insert data into the employee table
connection.commit()
connection.close() #close the connection

11) Now run the above program (on the Eclipse’s console) and entering those information when the program asks you to do so, press the enter key on your keyboard after each answer!

Eclipse Console
Eclipse Console

12) There should be a employeedata.sqlite database file in your src folder at this moment.

employeedata.sqlite
employeedata.sqlite

13) Open it up, do you see this?

sqlite3
sqlite3

14) As you can see the file above is not meant for human to read it. We need a software which can read those information we have inserted into the employee table. Go ahead and download this powerful db browser from http://sqlitebrowser.org and then open it up once you have downloaded and installed it on your pc.

15) Now open up the sqlitebrowser then load the sqlite database you have just created through File->Open DataBase. Under the Database Structure tab there are two tables, 1) the sqlite_sequence table is use to keep all the tables of the employeedata.sqlite database, 2) the employee table is the one which we have just created.

Open the database
Open the database

16) Switch to the Browse Data tab and select the employee table under the drop down list, you should now see the data that we have inserted into this table.

The are two John here...
The are two John here…

Do you realize that there are two persons with the same name “John” in the employee table? As you can see the only data which is unique in our table is the id and the rest of the field can consist of the same repeated value because we have programmed the table in such a way.

17) There are lots of thing you can do with sqlitebrowser besides the above mentioned feature, for example you can learn sql by directly entering the sql command into the console as shown below.

Enter SQL command here
Enter SQL command here

One thing you might feel puzzling about is why do we need to use the PersonalData class to create the employeeObject instead of directly insert the data returns by the input function into the table? Actually we do not need the employeeObject at all but this step will prepare you for my next python lesson which will involve both the employeeObject and the sqlite3 module.

Here is the complete script for the insertEmployeeData.py module

import sqlite3
db_name = 'employeedata.sqlite'

from os import path
import sys
sys.path.append(path.abspath('../MyClassDemo')) #let Eclipse knows where to find MyClassDemo project path

from MyClass.personaldata import PersonalData 

if __name__ == '__main__':
    
    name = input("Enter Employee Name")
    job = input("Enter Employee Job")
    salary = input("Enter Employee Salary")
    
    employeeObject = PersonalData(name, job, salary)
    
    # connect to the employeedata.sqlite database, if the database is not there yet then create it
    connection = sqlite3.connect('employeedata.sqlite')
    cursor = connection.cursor()
    
    # retrieve the employee table name which we have created
    cursor.execute("""SELECT name FROM sqlite_sequence WHERE name='employee'""")
    name_is_there = cursor.fetchone()[0]
    
    # if the employee table has not been created yet then we shall create it
    if name_is_there != 'employee':
        cursor.execute("""CREATE TABLE employee (
                        id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                        name TEXT NOT NULL,
                        job  TEXT NOT NULL,
                        salary TEXT NOT NULL) """)
        
    cursor.execute("INSERT INTO employee (name, job, salary) VALUES (?, ?, ?)", (employeeObject.name, employeeObject.work, employeeObject.earning)) # insert data into the employee table
    connection.commit()
    connection.close()

Do you enjoy this tutorial? If so don’t forget to subscribe to my blog for more future tutorial on Python, Javascript, Blender and many more…