Use SQL in Python

Importing SQLite and Pandas

First, we need to import SQLite for running SQL in Python.

Connecting Your Database

Now next step after importing libraries is to use the connect() function which creates a database in the environment. By connect(), the user can give the database name, which can be used in the program later. Connection is maintained by the connect() until we work with the database.

When one user is connected to the database, then no other user is allowed to access the database at the same time. So you must close the connection after performing your database operations.

Suppose we are here using the database name college.db. So for this database, we will write the code as given below:

Cursor Object

The next step is to use the cursor() function which helps in the execution of the queries.

Executing SQLite3 Queries – Creating Tables

Now after database connection and after cursor object creation next step is to execute queries of SQL in Python.

For query execution, we need to create a variable and inside that variable, we need to write the SQL query we want to execute. And then call the execute() method on the cursor and pass the variable name of the query as an argument inside the execute() method. After executing all the queries, commit the changes so that they can be saved in the database. And then the last step is to close the connection.

Example:

Below is an example program for creating a Student table inside the college database which has the attributes Student_Id, Student_FName, Student_LName, Student_Gender, and Student_Dob.

After executing the program we can check our database, table will be created in our database.

Inserting Into Table

For inserting data into the table, we need to create the variable again for the insert command, then we will call the execute() method on the cursor and pass the insert command variable as an argument.

Example:

Below is an example of inserting the data into the Student table we have created in the previous example.

Now we can check our database whether the data is inserted in the table or not.

Fetching Data

Till we have created the table and inserted some data inside it, now we want to fetch the data from the table, then is it as simple as we have executed the insert and create queries. We simply need to call execute() for the select command, and the fetchall() is required to be called on the cursor, then it will fetch all the records of the table in the form of a list.

Example:

Below is an example of fetching the data from Student using the fetchall() method.

Updating Data

Update SQL query is used for updating the data stored in the table. We can perform an update operation on a single column or multiple columns using the update statement.

A syntax of the update statement is given below:

In the above update statement, the set is used for setting the values of the columns, and where clause is used to specify the condition for the rows on which the update operation is required to be performed.

Example:

Below is an example of updating the Student table data, here we are updating the Student_LName of the Student having the Student_Id=1

Now we can check our database, to confirm whether data is updated in the database or not.

Selecting Records

We can select a record from the table as we have performed the fetch operation, we just need to simply specify the condition for selecting the record from the table inside the query using the where clause.

Example:

Below is an example to select the data from the Student table having the Student_Gender='F'.

Deleting Table Records

The delete command is used for deleting the data from the table, the syntax of the delete command is given below:

Here in the above syntax where clause is used to specify the condition for deleting the records.

Example:

Below is an example of deleting the record from the Student table having the Student_Id=1.

Now we can check our database, to confirm whether data is deleted from the table or not.