Run SQL in Jupyter Notebooks - Python Recipes

how do i run sql in jupyter notebooks hello welcome to tech minded recipes today we will learn how to run sql commands on jupyter notebooks you may be asking yourself why would i want to do that let me give you a few examples you may want

to do a quick analysis directly from a database i find it that sometimes it is just easier to write a quick sequel than to set up a data frame this is especially true when you have to join one or more tables either way this can be a handy tool on your tool belt to use in the future before we start if you have not yet subscribed the channel please click on subscribe to be informed

about more videos like this and if you like this video please give us our like at the end also please see below in the description to a link to the notebook with all the code in this video if you are watching this tutorial i assume you're already familiar with jupyter notebooks and have it installed the first thing we will do is to import the two libraries we will use

we will use the pandas library import pandas spd and then we will also import the sqlite library to allow us to create a database and do the demo import sqlite3 if you are just connecting to an existing database you will not need to take this step instead you will need to import the

specific library to connect with the database you will be using this could be oracle my sql or whatever other database with a python api next you will need to install the library i python sql this library will enable you to run sql in jupyter using jupyter magic commands

you don't need to understand what i will do right now if you do perfect but if you don't do not worry i will create a quick data frame just to create demo data that i will then inject into the sql lite database so we have data there to query df equals pde.data frame then you will create a dictionary with name age and city as keys those

will serve as the headers of our table the values of the dictionary will be the rows of each column last we will just call the data frame to check we have data yes there you go this is the data frame we just created now we will use the connect method of the sqlite library to connect to the database here since we actually do not have this database yet the connection command will create a database

if you are using an existing database it will just create a connection that you will use later once you have the connection created we can just use the to underscore sql method from the pandas data frame to add the data to the database in this case we are only using two arguments of the method the first one is the name of the table that will be created

i am naming it people and the second one is just the variable i created to hold the connection once i ran it we will have a table in our database that we can then use to run our queries until now all we did was to set up the data in the database you obviously will not need to do this if you're

working with real data not we will run a jupiter line magic to load the sql module so jupiter can use it you can recognize line magics by looking at the percent sign in the beginning those are different from cell magics you will see later that cell magics use two percent signs instead of one we again use a line magic here but this time to point the jupiter sql library to the database we

want to use that's it now we are pretty much ready to run our queries we will use the notation of two percent signs to create a cell magic followed by sql then just run our queries let's run a couple of examples select star from people there we can see we get all the records we had created

let's count the number of records in the table select count star from people there you go one last time let's sum the age of the people in the table select sum of age as age underscore sum from people once again as expected we are able to run sql directly from jupiter i hope you enjoyed this recipe i would love to see you again here in the future

do not forget to like this video and to press the subscribe button to watch more videos like this you