Top SQL Queries in Python Every Python Developer Should Know

SQL stands for Structured Query language that helps in managing, manipulating, and storing relational databases. A Relational database comprises collections of tables that are correlated in one or another form. Multiple tables allow us in segregating different entities of the target information and avoiding hundreds of columns being clubbed in one table that could make it difficult to manage the database.

It is a query language and not the database itself. These queries can be performed on any relational database management system like Oracle, Microsoft SQL Server, but there is a pythonic approach to create and execute SQL queries.

SQLite3 

This is the Python built-in library that allows us to create a lightweight database without the need of running an actual client-server model. It can be used internally by the applications to store data and execute SQL commands. The package is easy to use and allows to replicate the same schema in any production-based database like Postgresql. Let’s see how to set up the database.

• Standard import statement:

import sqlite3 as sq

The library is imported as an alias “sq”.

• Now, we need to create a connection to the database. The connection object will serve this purpose:

connection = sq.connect(“example.db”)

If this file is not created before, then it will be created as soon as this command is executed.

• To access and fetch information from the database, a cursor object needs to be created.

cursor = connection.cursor()

Now that the basic setup has been done, we can push the data into the database by creating the respective tables, but before that, let’s revise some SQL commands.

Basic Commands in SQL

SQL is excellent in terms of English readable commands. These are usually typed in upper case and there is no restriction about this. This is usually practiced so that the query keywords are highlighted, and they become distinguishable from table attributes. But as now we have powerful IDEs that highlight them in a different color, any method would work.

1. Create Table

This allows you to create the data holding objects, tables in your database. A database can have many tables with one or many relations. The tables have headers for every column that describes the value it holds. While creating the table, you need to specify the column name, type of data it will hold, and size. You can add other attributes such as if the column is a primary key, can have null values, and so on.

A basic table can be created as:

CREATE TABLE company_data (

id int,

name varchar (24),

no_of_employee int

);

2. SELECT and FROM

As the name suggests, it is used to select and fetch the data from the tables. It is the foremost step that would allow you to have a look at the data. It is similar to the Pandas head or tail function.

A simple query using these commands would look like this:

SELECT <column_name(s)>

FROM <table_name>;

To fetch all the columns from the table, you can put the star (*) instead of all the column names. The LIMIT keyword helps in defining the maximum number of rows. Consider this query:

SELECT *

FROM example_table

LIMIT 5;

Our learners also read: Learn Python Online for Free

3. Where

The where clause is used to specify the additional conditions over the query. You can include the range for columns, any specific comparison, or negations.

For example:

SELECT client_name, company_name

FROM company_data

WHERE company_name = ‘upGrad’;

4. Insert

This clause is used to add the values to the data tables. The values are inserted in the same order as the column names are added into the table in the create table command. One can insert multiple rows or one at a time.

For instance, the query below inserts 3 records into a table:

INSERT INTO company_data (id, name, no_of_employees)

VALUES

(1, ‘companyz_A’, 100),

(2, ‘company_B’, 200),

(3, ‘company_C’, 500);

Executing via Python

Now that you are aware of the commands, it’s time to execute them in the code. So far we are connected to the database and created a cursor object. This object will help in executing the commands on the database:

cursor.execute(‘‘‘ <QUERY> ’’’)

The query is passed as a string and in the triple quotes. This change will be not be reflected in the database until you commit this change. Closing the connection to the database at the end ensures that no new executions will be made after this point.

Look at this simple code:

import sqlite3 as sq

connection = sq.connect(“example.db”)

cursor = connection.cursor()

cursor.execute(”’

CREATE TABLE table1 (

id int,

name varchar (24)

)

”’)

cursor.execute(”’

INSERT INTO table1 (id, name)

VALUES

(1, ‘upgrad’),

(2, ‘blogs’)

”’)

cursor.execute(”’

SELECT *

FROM table1

”’)

connection.commit()

connection.close()

Important Tips

1. As the queries are passed as strings, modifying them using f-string may seem like a good option to make the program dynamic and user-controlled, but it can cause some security issues such as SQL injection attacks. To avoid these, use the placeholder “?” wherever you want to replace some values in the query. For instance:

cursor.execute(‘“SELECT ? FROM ?’’’, col_name, table_name)

You can ask the user to provide the column name and that value will be replaced here.

2. Use functions such as fetchone() and fetchall() to iterate over the results. Fetchone returns the next row in the query and fetchall returns all the rows fetched by the cursor. The results are returned as tuple so they cannot be modified by external functions in the program.

upGrad’s Exclusive Data Science Webinar for you –

How upGrad helps for your Data Science Career?

 

 

3. If you want to add bulk values to the database, then you can use the executemany() function and pass the list of tuples containing the values to be added. For example:

to_add = [(1, ‘hello’) , (2, ‘World’)]

cursor.executemany(‘INSERT INTO table VALUES (?, ?)’, to_add)

Also Read: Python Project Ideas & Topics

Learn data science courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

Conclusion

SQL is the most preferred database language and easy to understand. Most beginners in Data Science are advised to practice SQL as it improves the data retrieval process and gives a glimpse of how Data Engineering is carried out. It is the standard language for any database manipulation and offers faster access to the database.

If you are curious to learn about data science, check out IIIT-B & upGrad’s PG Diploma in Data Science which is created for working professionals and offers 10+ case studies & projects, practical hands-on workshops, mentorship with industry experts, 1-on-1 with industry mentors, 400+ hours of learning and job assistance with top firms.