How to create table in a database in SQLite3 database engine in Python

hi, how are you today


In last post we discussed How  to create database in sqlite3 database engine in Python.

Today in continuation we will discuss, once database creation is over, how to create table in database.

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.

As discussed in previous post, the process to create data base we already know, so today we will take a step forward and learn how to create table in database.
Before moving forward, please take a look at previous post to know how to create database.
Syntax/Query to create table

there are 2 ways to create table

• with cursor object
• without cursor object( directly with connection )

What is Cursor object?
In simple words, cursor object is required to perform all the operation on database.

• Creating table
• Inserting Data
• Reading data
• Updating data
• Deleting Data

[*Note: curs is optional to create table and insert data in database but mandatory to read, update and delete dta from database]

With Cursor Object

 curs=conn.cursor()  
 curs.execute("create table table_name (col_name type_of_data primary key, col_name type of data, col_name type of data,.....)")  

Without Cursor Object

 conn.execute("create table table_name (col_name type_of_data primary key, col_name type of data, col_name type of data,.....)")  

So conn is connection with database and now we are executing a query to create table in database using execute()
col_name : the name you want to give to the column i.e, Name, Idno, Address, Contact No, Gender etc
type_of_data: whether the data you want to save is of text(string), number(integer) or real(float).
primary key: It is unique and hence duplication not allowed, so if you mention any column primary key, its data will be treated as set and you cannot save the data with same primary key again.

In simple words, Employee Id, Bank Acc no are unique for every single person, so these are set as primary key in database so if by mistake we are entering the same value again, we will be informed that data cannot be saved as it's already present.
I will update a separate post for this.

Lets understand it with an example and we are taking an example of student database and in all the further post which covers

• How to insert Data
• How to search Data
• How to update Data
• How to delete Data

will work with same student database so it will be helpfull for you to understand all formats easily.

combine code for how to create database and table

1:  import sqlite3 as sql  
2:  conn=sql.connect("mydatabase.db")  
3:  curs= conn.cursor()  
4:  curs.execute("create table St_data (Student_Id number primary key, Student_name text, student_class number, Student_scholarship real)")  
5:  print("Table Created")  
6:  conn.close()  

or

1:  import sqlite3 as sql  
2:  conn=sql.connect("mydatabase.db")  
3:  conn.execute("create table St_data (Student_Id number primary key, Student_name text, student_class number, Student_scholarship real)")  
4:  print("conn")  
5:  conn.close()  

Here if we try to create a table that already exists, then we will get an exception, so we can use a query to overcome this issue

 curs=conn.cursor()  
 curs.execute("create table if not exist table_name(col_name type_of_data primary key, col_name type of data, col_name type of data,.....)")  

As already said, cursor object is optional for creating table and inserting data in database but better to make a habit of using cursor.

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

 conn.close()  

Hope you all like this post and I wish this proves useful to you.
Keep sharing & give your views about post in comments.
Comments are always welcomed for better improvement.

Thanks :)