Introduction to Python SQLite Create Database
Relational databases are quite popular when it comes to data ingestion, management, manipulation, etc. to create the final set of tables from the huge dump of data. A variety of RDBMS exists in the market like IBM DB2, My SQL, etc. This kind of RDBMS can be termed as SQLite, and these are quite famous due to many reasons.
- Lightweight & Fully transactional
- Serverless and not so complex setup
However, everything comes up with some limitations & so does SQLite. Such as it does not support some specific kind of joins, namely fully outer / Right Join. If you hold expertise with SQL and you want to utilize the same within Python. Then the same can be integrated within Python.
Syntax:
## Importing sqlite3 library so that we can utilize its functions
import sqlite3
sqlite3.connect('Type your DataBase name here.db')- Here we are utilizing the connect() function from the sqlite3 library in order to create a database in SQLite via Python.
- You need to pass as an argument as the name of the new database that you wish to create.
Before that, you need to connect to the database utilizing the connect() function available in SQLite3. Connect() function expects the path of you RDBMS you want to connect to as an argument.
To utilize this function, you need to import the sqlite3 library in python as well as below:
Code:
#Python program to demonstrate the usage of SQLite methods
import sqlite3
con = sqlite3.connect('EDUCBA.db')
print("Connected Successfully to SQLite")Output:
After having a successful connection with the database, all you need to do is create a cursor() object & call its execute() method to execute the SQL Queries.
Let us go through this example in order to understand this concept:
Code:
## Creating cursor object and naming it as cur
cur = con.cursor()
print("cursor created successfully")Output:
This is how you connect to an already existing database, and once you create a cursor, you can use the execute function to run SQL queries in Python via SQLite.
How to Create a New Database Using Python SQLite?
As soon as you create a connection via Python SQLite, a database file is generated on its own, even if in case it’s not present there in the database. Suppose you want a create a new database named ‘DBEDUCBA’, then the same can be implemented using the below program.
Code:
## Python program to demonstrate, How to create a new database via Python SQLite
## Importing SQLite and its predefined classes
import sqlite3
from sqlite3 import Error
## Creating a function named sql_connection
def sql_conn():
try:
con = sqlite3.connect('DBEDUCBA')
print("Connection has been successfully established: DB is created as well")
except Error:
print(Error)
finally:
## closing the connection established with the database
con.close()
##Calling the function that we have defined earlier
sql_conn()Output:
The connection has been successfully established: DB is created as well.
How Does this Work?
Taking the reference of the above piece of code:
import sqlite3
from sqlite3 import Error- Here we are importing the required python libraries and classed, namely sqlite3 and Error class, from the same library.
## Creating a function named sql_connection
def sql_conn():- In this function, we have to try and exception block
- If in case the connection is successful, the exception block does not come into the picture
- But if the connection fails, Then to avoid the failure of this piece of code, we have the except block, which prints the exact code that’s returned in case of a failed connection request.
con.close()- Finally, we are closing the connecting using the close function by utilizing the cursor object.
Now, what if we want to create a database in memory that is in RAM instead of disk. How can this be done?
Let us understand that in detail utilizing:
This can be achieved by having a slight modification to the one we discussed earlier. All we need to do is pass ‘:memory:’ as an argument to the connect function, which will enable the SQLite to create the in-memory table.
Code:
## Python program to demonstrate, How to create a new database via Python SQLite
## Importing SQLite and its predefined classes
import sqlite3
from sqlite3 import Error
## Creating a function named sql_connection
def sql_conn():
try:
con = sqlite3.connect(':memory:')
print("Connection has been successfully established and tables has been created in RAM ")
except Error:
print(Error)
finally:
## closing the connection eshtablished with the database
con.close()
#Calling the function that we have defined earlier
sql_conn()Output:
The connection has been successfully established, and the table has been created in RAM.
What if the Database with that Name Already Exists?
It may also happen that the name of the database you are passing as an argument in order to create a new one may already exist beforehand itself.
What will happen in that case?
Yes, a connection will be created with that database, and you can operate on the same using the execute() function. Let us go through the below example to understand more on this:
#Python program to demonstrate the usage of SQLite methods
import sqlite3
con = sqlite3.connect('EDUCBA.db')
print("connected to database successfully")
After having a successful connection with the database, all you need to do is create a cursor() object & call its execute() method to execute the SQL Queries.
## Creating cursor object and namimg it as cur
cur = con.cursor()
cur.execute('SELECT * from countries')Conclusion
Here we are utilizing the connect() function from the sqlite3 library in order to create a database in SQLite via Python. You need to pass as an argument as the name of the new database that you wish to create. As soon as you create a connection via Python SQLite, a database file is generated on its own, even if in case it’s not present there in the database. So this is how you can create a database.
Recommended Articles
We hope that this EDUCBA information on “Python SQLite Create Database” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
