Exploring Sqlite Database and table connection of python

In our python learning series, we will continue some of the other modules which are going to help in learning python. As I have posted many articles related to python basic concept and I hope it helps you a lot in learning python.  As my last article, we have learned about the Iterator concept. If you haven’t checked then I would recommend checking the post from this link.  I continue the series, today we will learn about the major used module in python is Database connection and uses of python.

In python, we can use any database API like Mysql, SQL server etc. But today we will learn about the sqlite3 database module which is mostly used.  To use the Sqlite3 module we need to install the Sqlite3 module by using pip command in the virtual environment. Before installing the SQLite package please check which python version running in your system.

If you are using the 2.7.3 version then use this command to install.

For version 3.0, run this command.

Once you install the above package then you can use or import this module in your project and use this feature easily. Now let’s see what are the methods available in the SQLite module.

Methods:

Now we need to understand about the Database Connection Object and their method to uses.

Database: Connection object

Connection Object returned by calling sqlite3.Connection().

Methods:

Database: Cursor object

Cursor Object returned by calling connection.cursor()

Statement:

Methods:

Now let’s see the few examples of SQLite to create a connection between database and table. We will create the table and insert some data and fetch those data from the SQL query. The most import thing that once you have a connection object that executed properly then you need to close the connection in the last to avoid the leaks.

As in the above example, we can see that we need to import the SQLite module package to use the feature. We need to create a connection with the named database.  Now we need to get the cursor object to create the table and insert data in the table. Once you have done then commit the transaction to execute data inserted and after please close the connection.

Now if you want to execute a few more data inserts or fetch or delete then you need to get the connection object again and use the cursor object of that. I hope it is very clear to understand. If still having confusion then let’s see one more example for cursor object for use memory rather than a database, we can directly use the memory to do few transactions. For few transactions is okay but it is not recommended to use huge transaction or data to store in memory, it will affect the performance and storage issue.

Now it is very understood to use the database cursor object. For advance, we can the Cursor Adapter to convert from the SQL to python. But I am not going into detail of Cursor Adapter here, I will come with the new article for this. In this article, we have covered and learned the basic of cursor object statement to execute the transaction.

Summary: Now we have a good understanding of the Database connection SQLite3 module. We have seen a few examples and play around it to clear the doubts. If you still having issue or doubts then please add a comment I will try to reply as much possible. In my next python tutorial series, we will learn about the Threading concept with the example.

If you are wondering to learn Android then Please Learn from Android category and wondering to lean Kotlin then Kotlin Category will help you. If you learn all python article then learn from the python category.

Please do subscribe your email to get the newsletter on this blog on below and if you like this post then do not forget to share like and comment on the below section.

Happy Coding 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.