Why you should learn SQL…? & SQLite 3 in python.

Learning SQL can not only enhance your skills, but it can also give you a better understanding of applications you work with on a daily basis.

SQL is a powerful and robust tool for extracting relevant and useful data from a large data set. While SQL has traditionally been the specialty of highly-trained data analysts and programmers, it’s finding greater acceptance among non-technical personnel. And there are many good reasons for that.

One of the most common database structures today is the relational database. And going hand-in-hand with relational databases is Structured Querying Language or SQL (pronounced S-Q-L or sequel).

SQ LITE 3 IN PYTHON.

SQLite 3 is easy to learn. As we discussed above, unlike learning a foreign language or a hardcore programming language, SQL is quite simple to learn.

Most importantly, SQLite is built-in into a Python library. In other words, you don’t need to install any server-side/client-side software, and you don’t need to keep something running as a service, as long as you imported the library in Python and start coding, then you have a relational database management system!

SQLite 3

The Sqlite3 module facilitates the use of SQLite databases with Python.

When we say “built-in”, it means that you don’t even need to run pip install to acquire the library. Simply import it by:

import Sqlite 

Create a Connection to DB

Don’t be bothered with the drivers, connection strings and so on. You can create an SQLite database and have a connection object as simple as:

con = sqlite3.connect('my-app.db')

So after we run this line of code, we have created the database and connected it to it already. This is because the database we asked Python to connect to is not existing so that it automatically created an empty one. Otherwise, we can use the same code to connect to an existing database.

Always, database name should be unique within the RDBMS.

Create a Table:

Then, let’s create a table.

SQLite CREATE TABLE statement is used to create a new table in any of the given database. Creating a basic table involves naming the table and defining its columns and each column’s data type.

Syntax:

Following is the basic syntax of CREATE TABLE statement.

CREATE TABLE user(
   column 1 datatype PRIMARY KEY(one or more columns),
   column 2 datatype,
   column 3 datatype,
   .....
   column N datatype
);

CREATE TABLE is the keyword telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Optionally, you can specify database_name along with table_name.

Insert Records:

Let’s insert some records into the USER table we just created, which can also prove that we indeed created it.

Suppose we want to insert multiple entries in one go. SQLite in Python can achieve this easily.

SQL = 'INSERT INTO USER (id, name, age) values(?, ?, ?)'
data = [
    (1, 'Alice', 21),
    (2, 'Bob', 22),
    (3, 'Chris', 23)
]

Now we’ll see what is connect method and cursor object…

1.connect() method is used that returns a Connection object.

Where it accepts a path to the existing database. If no database exists, it will create a new database on the given path.

2. Cursor object is generated using the cursor() method.

Where it allows you to execute queries against a database.

The COMMIT Command

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.

The CLOSE Command

Closes an open cursor by releasing the current result set and freeing any cursor locks held on the rows on which the cursor is positioned. CLOSE leaves the data structures available for reopening, but fetches and positioned updates are not allowed until the cursor is reopened.

What are SQL CRUD operations?

All operations you can do on any data can be boiled down to Create, Read, Update, and Delete (CRUD). You can create something new, you can read it, update it, and finally delete it if you wish.

Create:

To create a new row in a table, the INSERT INTO statement is used and looks something like:

INSERT INTO table_name (column 1, column 2, column 3 ) VALUES (value 1, value 2, value 3);

Read:

To read the rows from a table, the SELECT statement is used and looks something like:

SELECT column1, column2 FROM table_name;

To get all of the movies from a database, you can do something like this:

SELECT title, description FROM movie_entity;

If you want to get more specific, the WHERE clause can help.

Update:

To update a row in a table, the UPDATE statement is used and looks something like this:

UPDATE table_name SET column 1 = value 1, column 2 = value 2 WHERE condition;

Delete:

To delete a record from the table, the DELETE statement is used and looks something like this:

DELETE FROM table_name WHERE condition;

Now Let’s see some of the example’s

EXAMPLE 1:

EXAMPLE 2:

Output:

[(112, ‘m1’, 3, ‘chennai’), (1113, ‘m2’, 4, ‘chennai’), (11222, ‘m3’, 5, ‘banglore’), (344, ‘m2’, 4, ‘banglore’), (222, ‘m4’, 7, ‘chennai’), (3344, ‘m3’, 4, ‘chennai’), (33444, ‘m3’, 1, ‘chennai’), (444, ‘m4’, 6, ‘chennai’), (4, ‘m3’, 8, ‘chennai’), (333, ‘m2’, 4, ‘chennai’)] [(4, ‘m3’, 8, ‘chennai’), (222, ‘m4’, 7, ‘chennai’), (444, ‘m4’, 6, ‘chennai’), (11222, ‘m3’, 5, ‘banglore’), (1113, ‘m2’, 4, ‘chennai’), (344, ‘m2’, 4, ‘banglore’), (3344, ‘m3’, 4, ‘chennai’), (333, ‘m2’, 4, ‘chennai’), (112, ‘m1’, 3, ‘chennai’), (33444, ‘m3’, 1, ‘chennai’)] [(‘banglore’, 4), (‘chennai’, 4)]

So hope you understand the concepts will catch you all with the next blog…

If you liked then appreciate the writing, 👏👏 clap, and share.

You can connect with me on LinkedIn.