How to create Table in SQLite3 Database using Python

 

Creating Table in SQLite3 Database in Python

What is a Table?

Table is a collection of row and columns which are used to stored data in a specific format.
Point to be remember is, size of column(s) should be carefully decided at the time of table creation as it is  not possible to add new column via programming without deleting the existing table.

Query to create Table

 "create table Table_name (Column_name type_of_data primary_key, Column_name type_of_data,.....)"
                                                or
 "CREATE TABLE Table_name (Column_name type_of_data primary_key, Column_name type_of_data,.....)"

1. Column_name : the name you want to give to the column i.e, Name, Idno, Address, Contact No, Gender etc
2. type_of_data: whether the data you want to save is of text(string), number(integer) or real(float).
3. primary key: It is unique and hence duplication not allowed, 
4. Column mentioned as primary key, data will be treated as set and you cannot save the data with same primary key again.
5. Primary key column performs major role in accessing data in database.

Lets understand this as an example

In any company, an employee will have many details related to his existence in company like

• Employee_ID
• Employee_Name
• Employee_Department
• Employee_Skills
• Employee_Salary

Apart than Employee_ID, rest things can be common for 2 or more employees but, Employee_ID has to be unique, which helps Employer to check all his progress.

So we need keep Employee_ID as primary key, so that by mistake also, ID will not repeat for 2 employees.

Search on base of other column_name can be done, but if  search is done base on Employee_Name, chances of getting data for more than one Employee. 

Example on How to create Table :

 # program to create table

 import sqlite3 as sql

 conn = sql.connect("Employee_Details.db")     # creating connection with database(created in current directory)
 curs = conn.cursor()                    # creating cursor to database

 curs.execute("create table Employee_Data(Id number primary key, Name text, Salary number)")
 print("Table created successfully")

 conn.close()

 #Output

 Table created successfully

Lets have a look in BD_Browser

Always remember to close the connection before closing or quitting your software