Everything you need to know about Embedded SQL

Shivam Verma
May 3 · 3 min read
Google Images by morioh.com

Embedded SQL is a way of combining the computing power of a programming language, eg: Python, Java, C++, etc., and the database manipulation capabilities of SQL; we will be having hands-on experience with a MySQL database, but you can use any SQL database, e.g., Postgresql, MariaDB, etc.

Embedded SQL statements are SQL statements written inline with the program source code of the host language.

Basically, we will be interacting with a database using a programming language. So, let’s look at an example; we will create a database on our local system and interact with it using Python(you can use any language of your preference).

Starting with opening up a MySQL command-line interface or if you are on a Linux system, open your terminal,

Connect with your local database as a root or any other username like this,

sudo mysql -u username -p password

You must know the username and password from the first time you installed and set MySQL in your system. The above command is for Linux users to enable the MySQL command-line interface from the terminal. You can directly open the MySQL cmd and start interacting with your database.

Now follow the steps below on your MySQL command-line interface.

Step 1: Create a new Database named, let’s say, ‘school.’:

create database school;

Step 2: Select this database:

use school;

Now, every command will work on this database, ‘school.’

Step 3: Create a table, just so that we can do some testing on it using Python:

create table student ( 
id mediumint not null
);

Now, we can start writing some code.

Firstly, install some libraries on your system; by running this command on your cmd/terminal on your system:

pip install mysql-connector
pip install mysql-connector-python

Now, have a look at the following code:

Using mysql.connector.connect function, connect with the local database you just created by passing the credentials as arguments.

And then define a cursor on this database that will help us execute some SQL queries later by doing database.cursor().

Now that you are connected with your database and have your cursor let’s execute some queries.

You can run any query by using the execute function:

curr.execute("WRITE YOUR QUERY HERE")

Let’s run a “show tables;” query to check the list of tables in this database:

After using the execute function, we made use of the fetchall() function, which returns a python list, to extract the data out of this query in the ‘data’ named variable and printed the elements of this list which would be containing the names of the tables in the database.

After executing this program, you will see the table name student we created earlier in the ‘school’ database.

That’s it. You created a database and interacted with it using Python, that's what we wanted, and that’s what Embedded SQL does for us.

Conclusion

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data…

Sign up for Analytics Vidhya News Bytes

By Analytics Vidhya

Latest news from Analytics Vidhya on our Hackathons and some of our best articles! Take a look.

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Shivam Verma

Written by

Computer Science Undergraduate @IIITD

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Shivam Verma

Written by

Computer Science Undergraduate @IIITD

Analytics Vidhya

Analytics Vidhya is a community of Analytics and Data Science professionals. We are building the next-gen data science ecosystem https://www.analyticsvidhya.com

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store