{"id":3555,"date":"2021-06-09T19:07:27","date_gmt":"2021-06-09T19:07:27","guid":{"rendered":"https:\/\/cloudxlab.com\/blog\/?p=3555"},"modified":"2021-06-09T19:07:28","modified_gmt":"2021-06-09T19:07:28","slug":"how-to-access-databases-using-jupyter-notebook","status":"publish","type":"post","link":"https:\/\/cloudxlab.com\/blog\/how-to-access-databases-using-jupyter-notebook\/","title":{"rendered":"How to access databases using Jupyter Notebook"},"content":{"rendered":"\n

SQL is a very important skill. You not only can access the relational databases but also big data using Hive<\/code>, Spark-SQL<\/code> etcetera. Learning SQL could help you excel in various roles such as Business Analytics, Web Developer, Mobile Developer, Data Engineer, Data Scientist, and Data Analyst. Therefore having access to SQL client is very important via browser. In this blog, we are going to walk through the examples of interacting with SQLite and MySQL using Jupyter notebook.<\/p>\n\n\n\n

A Jupyter notebook is a great tool for analytics and interactive computing. You can interact with various tools such as Python, Linux, File System, Scala, Lua, Spark, R, and SQL from the comfort of the browser. For almost every interactive tool, there is a kernel in Jupyter. Let us walk through how would you use SQL<\/code> to interact with various databases from the comfort of your browser. <\/p>\n\n\n\n

\"\"
Using Jupyter to access databases such SQLite and MySQL.<\/figcaption><\/figure>\n\n\n\n\n\n\n\n

Installation<\/h2>\n\n\n\n

The kernel that we are going to use is ipython-sql<\/a>. If you are using CloudxLab environment, you don’t need to install anything. You can just open “Python 3” notebook and start with rest.<\/p>\n\n\n\n

If you are not using CloudxLab, you will have to install ipython-sql using the following command:<\/p>\n\n\n\n

pip install ipython-sql<\/code><\/p>\n\n\n\n

Now, create a new notebook using Jupyter, New -> \"Python 3\"<\/kbd> on CloudxLab.<\/p>\n\n\n\n

Load Extension<\/h2>\n\n\n\n

The first thing you need to do is load the extension. Run the following command in the Jupyter notebook:<\/p>\n\n\n\n

%load_ext sql<\/code><\/p>\n\n\n\n

Interact with SQLite<\/h2>\n\n\n\n

SQLite<\/a> is a great light database. From their website, this description is very precise: <\/p>\n\n\n\n

The SQLite\u00a0file format<\/a>\u00a0is stable, cross-platform, and backward compatible and the developers pledge to keep it that way\u00a0through the year 2050<\/a>. SQLite database files are commonly used as containers to transfer rich content between systems\u00a0[1]<\/a>[2]<\/a>[3]<\/a>\u00a0and as a long-term archival format for data\u00a0[4]<\/a>.<\/p>www.sqlite.org<\/cite><\/blockquote>\n\n\n\n

Initialize connection<\/h3>\n\n\n\n

Once you have loaded the sql<\/code> extension, you can interact with it after initializing connection to. If you are on your own Jupyter installation not on CloudxLab, you will have to install SQLite and it’s driver. On CloudxLab, you can simply connect to an SQLite database using the following command.<\/p>\n\n\n\n

%sql sqlite:\/\/\/foo.db<\/code><\/p>\n\n\n\n

It will create a database file with the name foo.db<\/code> in your home directory, it is not existing already else it will simply initialize the connection to existing database.<\/p>\n\n\n\n

Create Table in SQLite<\/h3>\n\n\n\n

Let’s create a simple table `config_test` with two columns – name and value.<\/p>\n\n\n\n

 %%sql\n create table config_test(name varchar(20), value int) <\/code><\/pre>\n\n\n\n
Please note the ‘%’ twice before ‘sql’. Earlier we using only a single ‘%’.<\/p>\n\n\n\n
Insert data into a table in SQLite<\/h3>\n\n\n\n
We can insert the data into the table previously created using standard SQL commands.<\/p>\n\n\n\n
%%sql\ninsert into config_test values(\"sandeep\", 20);\ninsert into config_test values(\"sandeep\", 30);\ninsert into config_test values(\"giri\", 10)<\/code><\/pre>\n\n\n\n
In an SQL cell in the Jupyter notebook, you can add multiple SQL statements. Please make sure to end each statement with a semicolon. Though you can skip the semicolon on the last statement of the cell.<\/p>\n\n\n\n
Querying SQLite table<\/h3>\n\n\n\n
Of course, you can query using complex SQL in SQLite. The select statement would also require you to start the cell with %%sql.<\/p>\n\n\n\n
%%sql\nselect * from config_test<\/code><\/pre>\n\n\n\n
You can write any complex query in the cell. It would display the results in the following format.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Here is another example of the query.<\/p>\n\n\n\n
%%sql\n select name, sum(value), count(value) from config_test group by name<\/code><\/pre>\n\n\n\n
It basically groups the data by name and aggregates the value.<\/p>\n\n\n\n
To help you practice SQL, we have updated an SQLite DB to a shared location. We have copied the database file from here<\/a>. You can use that database with the following command.<\/p>\n\n\n\n
%sql sqlite:\/\/\/\/cxldata\/sqlite\/sf-food-inspections-lives.sqlite<\/code><\/pre>\n\n\n\n
Please note that there are four slashes after “sqlite:” in the Url. The first three slashes are part of the URL scheme and the last slash is for the absolute path because the database file sf-food-inspections-lives.sqlite<\/code> is located in the folder \/cxldata\/sqlite<\/code>.<\/p>\n\n\n\n
To find out which tables are there in this database, you can use the following command. The standard command “.tables” from the SQLite console will not work.<\/p>\n\n\n\n
%%sql\n SELECT name FROM sqlite_master WHERE type='table'<\/code><\/pre>\n\n\n\n
This would print inspection_records<\/code>.<\/p>\n\n\n\n
Now, you can practice querying this table. Here is a simple query:<\/p>\n\n\n\n
%%sql\nselect * from inspection_records limit 10;<\/code><\/pre>\n\n\n\n
Interact with MySQL using Jupyter notebook<\/h2>\n\n\n\n
Establish connection<\/h3>\n\n\n\n
In CloudxLab, we already have an installed MySQL database. The details of which you can find in My Lab -> SQL Credentials<\/a><\/p>\n\n\n\n
If you are doing it on your local machine, you might have to install MySQL database and the mysql driver in Jupyter notebook.<\/p>\n\n\n\n
The first thing you have to do is initialize a connection:<\/p>\n\n\n\n
%sql mysql:\/\/sqoopuser:NHkkP876rp@cxln2.c.thelab-240901.internal\/retail_db<\/code><\/pre>\n\n\n\n
Basically, the formal of connection URL is mysql:\/\/login:password@host\/databasename<\/kbd><\/p>\n\n\n\n
Querying MySQL<\/h3>\n\n\n\n
Now, you can run any SQL  query just like mentioned above. To find out about tables, you can run:<\/p>\n\n\n\n
%%sql\n show tables<\/code><\/pre>\n\n\n\n
It would display something like this:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To see what’s there in `customers` table, you can use:<\/p>\n\n\n\n
%%sql\n select * from customers limit 10;<\/code><\/pre>\n\n\n\n
You can interact with other databases in a similar fashion. You will have to use different connection strings. Please take a look at its documentation<\/a> for more details.<\/p>\n\n\n\n
To learn SQL, you can follow this SQL Tutorial<\/a>. <\/p>\n\n\n\n
Happy Learning!<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL is a very important skill. You not only can access the relational databases but also big data using Hive, Spark-SQL etcetera. Learning SQL could help you excel in various roles such as Business Analytics, Web Developer, Mobile Developer, Data Engineer, Data Scientist, and Data Analyst. Therefore having access to SQL client is very important … Continue reading “How to access databases using Jupyter Notebook”<\/span><\/a><\/p>\n","protected":false},"author":14,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[119,122,120,121],"yoast_head":"\nHow to access databases using Jupyter Notebook | CloudxLab Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/cloudxlab.com\/blog\/how-to-access-databases-using-jupyter-notebook\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to access databases using Jupyter Notebook | CloudxLab Blog\" \/>\n<meta property=\"og:description\" content=\"SQL is a very important skill. You not only can access the relational databases but also big data using Hive, Spark-SQL etcetera. Learning SQL could help you excel in various roles such as Business Analytics, Web Developer, Mobile Developer, Data Engineer, Data Scientist, and Data Analyst. Therefore having access to SQL client is very important … Continue reading "How to access databases using Jupyter Notebook"\" \/>\n<meta property=\"og:url\" content=\"https:\/\/cloudxlab.com\/blog\/how-to-access-databases-using-jupyter-notebook\/\" \/>\n<meta property=\"og:site_name\" content=\"CloudxLab Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/cloudxlab\" \/>\n<meta property=\"article:published_time\" content=\"2021-06-09T19:07:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-06-09T19:07:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.cloudxlab.com\/wp-content\/uploads\/2021\/06\/image-3.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@CloudxLab\" \/>\n<meta name=\"twitter:site\" content=\"@CloudxLab\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\">\n\t<meta name=\"twitter:data1\" content=\"5 minutes\">\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/cloudxlab.com\/blog\/#website\",\"url\":\"https:\/\/cloudxlab.com\/blog\/\",\"name\":\"CloudxLab Blog\",\"description\":\"Learn AI, Machine Learning, Deep Learning, Devops & Big Data\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/cloudxlab.com\/blog\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/cloudxlab.com\/blog\/how-to-access-databases-using-jupyter-notebook\/#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/blog.cloudxlab.com\/wp-content\/uploads\/2021\/06\/image-3.png\",\"contentUrl\":\"https:\/\/blog.cloudxlab.com\/wp-content\/uploads\/2021\/06\/image-3.png\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/cloudxlab.com\/blog\/how-to-access-databases-using-jupyter-notebook\/#webpage\",\"url\":\"https:\/\/cloudxlab.com\/blog\/how-to-access-databases-using-jupyter-notebook\/\",\"name\":\"How to access databases using Jupyter Notebook | CloudxLab Blog\",\"isPartOf\":{\"@id\":\"https:\/\/cloudxlab.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/cloudxlab.com\/blog\/how-to-access-databases-using-jupyter-notebook\/#primaryimage\"},\"datePublished\":\"2021-06-09T19:07:27+00:00\",\"dateModified\":\"2021-06-09T19:07:28+00:00\",\"author\":{\"@id\":\"https:\/\/cloudxlab.com\/blog\/#\/schema\/person\/4835f1b3d5000626cb15e9311d748e09\"},\"breadcrumb\":{\"@id\":\"https:\/\/cloudxlab.com\/blog\/how-to-access-databases-using-jupyter-notebook\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/cloudxlab.com\/blog\/how-to-access-databases-using-jupyter-notebook\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/cloudxlab.com\/blog\/how-to-access-databases-using-jupyter-notebook\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/cloudxlab.com\/blog\/\",\"url\":\"https:\/\/cloudxlab.com\/blog\/\",\"name\":\"Home\"}},{\"@type\":\"ListItem\",\"position\":2,\"item\":{\"@id\":\"https:\/\/cloudxlab.com\/blog\/how-to-access-databases-using-jupyter-notebook\/#webpage\"}}]},{\"@type\":\"Person\",\"@id\":\"https:\/\/cloudxlab.com\/blog\/#\/schema\/person\/4835f1b3d5000626cb15e9311d748e09\",\"name\":\"Sandeep Giri\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/cloudxlab.com\/blog\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1393214840cf7455bb4cba055cb30468?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1393214840cf7455bb4cba055cb30468?s=96&d=mm&r=g\",\"caption\":\"Sandeep Giri\"},\"sameAs\":[\"https:\/\/cloudxlab.com\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","_links":{"self":[{"href":"https:\/\/cloudxlab.com\/blog\/wp-json\/wp\/v2\/posts\/3555"}],"collection":[{"href":"https:\/\/cloudxlab.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cloudxlab.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cloudxlab.com\/blog\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/cloudxlab.com\/blog\/wp-json\/wp\/v2\/comments?post=3555"}],"version-history":[{"count":1,"href":"https:\/\/cloudxlab.com\/blog\/wp-json\/wp\/v2\/posts\/3555\/revisions"}],"predecessor-version":[{"id":3559,"href":"https:\/\/cloudxlab.com\/blog\/wp-json\/wp\/v2\/posts\/3555\/revisions\/3559"}],"wp:attachment":[{"href":"https:\/\/cloudxlab.com\/blog\/wp-json\/wp\/v2\/media?parent=3555"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cloudxlab.com\/blog\/wp-json\/wp\/v2\/categories?post=3555"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cloudxlab.com\/blog\/wp-json\/wp\/v2\/tags?post=3555"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}