Blog_Banner_Asset
    Homebreadcumb forward arrow iconBlogbreadcumb forward arrow iconData Sciencebreadcumb forward arrow iconTop SQL Queries in Python Every Python Developer Should Know

Top SQL Queries in Python Every Python Developer Should Know

Last updated:
29th Jan, 2021
Views
Read Time
6 Mins
share image icon
In this article
Chevron in toc
View All
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:

Top Essential Data Science Skills to Learn

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)

Read our popular Data Science Articles

VALUES

(1, ‘companyz_A’, 100),

(2, ‘company_B’, 200),

(3, ‘company_C’, 500);

Explore our Popular Data Science Degrees

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.

Profile

Rohit Sharma

Blog Author
Rohit Sharma is the Program Director for the UpGrad-IIIT Bangalore, PG Diploma Data Analytics Program.

Frequently Asked Questions (FAQs)

1What do you know about constraints in SQL?

Constraints determine what type of data will be entered in the table. They are specified during the creation of the table and can be applied to a single field or multiple fields. The constraints in SQL are:
NOT NULL- Prevents NULL value to be entered in a field.
CHECK- Checks if all the values in a field satisfy the given condition.
DEFAULT- Assigns a default value to a field automatically if no value has been entered.
UNIQUE- Allows only unique values to be entered in a field.
INDEX- Indexes a field providing faster retrieval of records.
PRIMARY KEY- Uniquely identifies each record in a table.
FOREIGN KEY- Restraints actions that could harm the links between tables.

2Explain an index and its types?

An index or a database index can be referred to as a special lookup table that makes the data retrieval operation much faster. It consumes more memory and additional writes in order to search the data faster. An index can be of 4 types:
1. Unique Index
2. Non-Unique Index
3. Clustered Index

3What is a relationship in SQL and what are its types?

A relation between multiple entities or multiple tangible and intangible objects that have something to do with each other is known as a relationship. There are 4 types of relationships in SQL:
1. One-to-One
2. One-to-Many/Many-to-One
3. Many-to-Many
4. Self-referencing relationships.

Explore Free Courses

Suggested Blogs

Most Common PySpark Interview Questions &#038; Answers [For Freshers &#038; Experienced]
20601
Attending a PySpark interview and wondering what are all the questions and discussions you will go through? Before attending a PySpark interview, it’s
Read More

by Rohit Sharma

05 Mar 2024

Data Science for Beginners: A Comprehensive Guide
5036
Data science is an important part of many industries today. Having worked as a data scientist for several years, I have witnessed the massive amounts
Read More

by Harish K

28 Feb 2024

6 Best Data Science Institutes in 2024 (Detailed Guide)
5113
Data science training is one of the most hyped skills in today’s world. Based on my experience as a data scientist, it’s evident that we are in
Read More

by Harish K

28 Feb 2024

Data Science Course Fees: The Roadmap to Your Analytics Career
5055
A data science course syllabus covers several basic and advanced concepts of statistics, data analytics, machine learning, and programming languages.
Read More

by Harish K

28 Feb 2024

Inheritance in Python | Python Inheritance [With Example]
17377
Python is one of the most popular programming languages. Despite a transition full of ups and downs from the Python 2 version to Python 3, the Object-
Read More

by Rohan Vats

27 Feb 2024

Data Mining Architecture: Components, Types &#038; Techniques
10660
Introduction Data mining is the process in which information that was previously unknown, which could be potentially very useful, is extracted from a
Read More

by Rohit Sharma

27 Feb 2024

6 Phases of Data Analytics Lifecycle Every Data Analyst Should Know About
79958
What is a Data Analytics Lifecycle? Data is crucial in today’s digital world. As it gets created, consumed, tested, processed, and reused, data goes
Read More

by Rohit Sharma

19 Feb 2024

Sorting in Data Structure: Categories &#038; Types [With Examples]
138280
The arrangement of data in a preferred order is called sorting in the data structure. By sorting data, it is easier to search through it quickly and e
Read More

by Rohit Sharma

19 Feb 2024

Data Science Vs Data Analytics: Difference Between Data Science and Data Analytics
68376
Summary: In this article, you will learn, Difference between Data Science and Data Analytics Job roles Skills Career perspectives Which one is right
Read More

by Rohit Sharma

19 Feb 2024

Schedule 1:1 free counsellingTalk to Career Expert
icon
footer sticky close icon