Hey, Python lovers (specifically programmers 😂 not snake lovers) here we are on a new topic of discussion and implementation:- “Sqlite – create table if not exists using Python”.
Now we all know about a very famous SQLite plugin sqlite3 which collaborates with the default Python environment. This helps us in making real-time applications and then connecting them to the database without using localhost or an online server.
We can call SQLite3 is a type of application that runs on a local machine without any config settings. So, to make things easier we are going to write a script that will check whether the table exists. If it does not exist it will automatically create one for us. This is just like making a smart tool. So, let us go for it!
Also read: Check If a Table Exists – Python SQLite3
Creating A Table If It Does Not Exist using Python SQLite3
Create a folder named Table Creation and then add the following code in a file in the same folder.
import sqlite3 connection = sqlite3.connect('database/school.db') # file path # create a cursor object from the cursor class cur = connection.cursor() cur.execute(''' CREATE TABLE stud_data( roll_no integer, name text, class integer, division text )''') print("\nDatabase created successfully!!!") # committing our connection connection.commit() # close our connection connection.close()
Database created successfully!!!
We created a school database that contains a student data table “stud_data”. The table has four columns: roll_no, name, class, and division. When we visualize it in SQLite studio, this looks like this:
Deleting the Table
Purposely we shall delete the table and then create our smart script.
Code to delete the table:
import sqlite3 connection = sqlite3.connect('database/school.db') connection.execute("DROP TABLE stud_data") print("Your table has been deleted!!!") connection.close()
The DROP TABLE “table_name” query drops/deletes the table. Make sure to add the respective table name.
Your table has been deleted!!!
Complete Code to Create and Delete a Table
This section contains the major script that will check if the table exists or is not in the database. If the case happens then a new table with the same name and parameters is created.
import sqlite3 connection = sqlite3.connect('database/school.db') cur = connection.cursor() try: cur.execute("SELECT * FROM stud_data") # storing the data in a list data_list = cur.fetchall() print('Roll_Number' + '\t Name') print('--------' + '\t\t-------------') for item in items: print(item + ' | ' + item + '\t' + item) except sqlite3.OperationalError: print("No such table: stud_data") if(sqlite3.OperationalError): # if this error occurs try: print("Creating a new table: ") cur.execute(''' CREATE TABLE stud_data( roll_no integer, name text, class integer, division text )''') print("New table created successfully!!!") print("Here are the contents of the table: \n1: roll_no. \n2: name \n3: class \n4:division.") except sqlite3.Error() as e: print(e, " occured") connection.commit() connection.close()
No such table: stud_data Creating a new table: New table created successfully!!! Here are the contents of the table: 1: roll_no. 2: name 3: class 4: division.
- We define two try blocks. The first one checks whether a table exists or not. If not the if condition jumps to the new try block and makes a new table for us.
- In the first try block: Using the SQLite query: “SELECT * FROM table_name” will try to fetch all the rows and columns from the table.
- If the table is absent try block throws sqlite.OperationalError. The except block handles it. The if() statement under it opens second try-except block.
- Then the second try statement accomplishes the task of creating a new table with the same parameters.
- The except block checks for any common error using sqlite.Error() method and handles it.
- The second part of the code just executes a query of creating a new table stud_data and inserting it into our database.
This is the way we can check whether a table exists in our SQLite database or not. It’s a recommendation to understand how the code works before implementing the solution. Thanks for reading.