{"id":21152,"date":"2021-02-03T03:53:25","date_gmt":"2021-02-03T03:53:25","guid":{"rendered":"https:\/\/holypython.com\/?page_id=21152"},"modified":"2021-03-28T00:58:53","modified_gmt":"2021-03-28T00:58:53","slug":"querying-a-database-with-python-sqlite","status":"publish","type":"page","link":"https:\/\/holypython.com\/python-sqlite-tutorial\/querying-a-database-with-python-sqlite\/","title":{"rendered":"Querying a Database with Python (SQLite)"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-page\" data-elementor-id=\"21152\" class=\"elementor elementor-21152\">\n\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-38819ef elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"38819ef\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-50 elementor-top-column elementor-element elementor-element-98d652e\" data-id=\"98d652e\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-b5b258d elementor-widget elementor-widget-text-editor\" data-id=\"b5b258d\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<ol><li><a href=\"https:\/\/holypython.com\/python-sqlite-tutorial\/sqlite-database-basics-with-db-browser-for-sqlite\/\" rel=\"noopener\">SQLite Database Basics with DB Browser for SQLite<\/a><\/li><li><a href=\"https:\/\/holypython.com\/python-sqlite-tutorial\/inserting-values-into-database-table\/\">INSERT INTO &amp; UPDATE<\/a><\/li><li><u>More SQL Syntax<\/u>:<ul><li><a href=\"https:\/\/holypython.com\/python-sqlite-tutorial\/sql-querying-basics\/\" rel=\"noopener\">SQL Querying: Basics<\/a><\/li><li><a href=\"https:\/\/holypython.com\/python-sqlite-tutorial\/sql-like-operator\/\">Querying: LIKE Operator<\/a><\/li><\/ul><\/li><li><a href=\"https:\/\/holypython.com\/python-sqlite-tutorial\/building-a-database-with-sql-python-sqlite\/\">Building a Database with Python (SQLite)<\/a><\/li><li><a href=\"https:\/\/holypython.com\/python-sqlite-tutorial\/querying-a-database-with-python-sqlite\/\">Querying a Database with Python (SQLite)<\/a><\/li><li><u>More SQLite Examples<\/u>:<ul><li><a href=\"https:\/\/holypython.com\/python-sqlite-tutorial\/python-magic-for-sql\/\">Python Magic for SQL<\/a><\/li><\/ul><\/li><li><a href=\"https:\/\/holypython.com\/python-sqlite-tutorial\/sql-native-visualization\/\">SQL Native Visualization<\/a><\/li><\/ol>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t<div class=\"elementor-column elementor-col-50 elementor-top-column elementor-element elementor-element-820bd33\" data-id=\"820bd33\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<section class=\"elementor-section elementor-inner-section elementor-element elementor-element-2eacc4c elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"2eacc4c\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-50 elementor-inner-column elementor-element elementor-element-1d7add4\" data-id=\"1d7add4\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-b3f3750 elementor-widget elementor-widget-menu-anchor\" data-id=\"b3f3750\" data-element_type=\"widget\" data-widget_type=\"menu-anchor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-menu-anchor\" id=\"intro\"><\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-ba8e0ac elementor-widget elementor-widget-heading\" data-id=\"ba8e0ac\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h1 class=\"elementor-heading-title elementor-size-default\">Querying a Database with Python (SQLite)<\/h1>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t<div class=\"elementor-column elementor-col-50 elementor-inner-column elementor-element elementor-element-3a3ccc8\" data-id=\"3a3ccc8\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-57329d4 elementor-widget elementor-widget-raven-image\" data-id=\"57329d4\" data-element_type=\"widget\" data-widget_type=\"raven-image.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"raven-image\">\r\n\t\t\t\t\t<figure class=\"wp-caption\">\r\n\t\t\r\n\t\t\r\n\t\t<img fetchpriority=\"high\" decoding=\"async\" width=\"286\" height=\"300\" src=\"https:\/\/holypython.com\/wp-content\/uploads\/2020\/02\/banner-286x300.png\" class=\"attachment-medium size-medium wp-image-7564\" alt=\"\" srcset=\"https:\/\/holypython.com\/wp-content\/uploads\/2020\/02\/banner-286x300.png 286w, https:\/\/holypython.com\/wp-content\/uploads\/2020\/02\/banner.png 623w\" sizes=\"(max-width: 286px) 100vw, 286px\" \/>\t\t\t<figcaption class=\"widget-image-caption wp-caption-text\">Python Tutorials<\/figcaption>\r\n\t\t\t\t\t\t<\/figure>\r\n\t\t\t\t\t<\/div>\r\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<div class=\"elementor-element elementor-element-98a498b elementor-widget elementor-widget-text-editor\" data-id=\"98a498b\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Contents<\/p><ul><li><a href=\"#intro\">Introduction<\/a><ol><li><a href=\"#database_connection\">Connecting to database and creating cursor<\/a><\/li><li><a href=\"#fetchone\">Fetching Database Rows (fetchone)<\/a><\/li><li><a href=\"#fetchall\">Fetching Database Rows (fetchall)<\/a><\/li><li><a href=\"#querying_with_python\">Database Querying Examples with Python<\/a><\/li><\/ol><\/li><li><a style=\"background-color: #ffffff;\" href=\"#sum\">Summary<\/a><\/li><\/ul>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-391e910 elementor-widget elementor-widget-menu-anchor\" data-id=\"391e910\" data-element_type=\"widget\" data-widget_type=\"menu-anchor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-menu-anchor\" id=\"tutorial\"><\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-e653fb5 elementor-widget elementor-widget-text-editor\" data-id=\"e653fb5\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<h2>Introduction<\/h2><p>In this SQL tutorial we will explore convenient querying possibilities with Python.<\/p><p>Once you&#8217;ve created an SQLite (or MySQL) Database you will likely want to query it as well. Or you might also want to access an existing database and retrieve data from it for your application, script, science project or data science project.<\/p><p>If you&#8217;re wondering how you can create a database for practicing, you can check out these tutorials:<\/p><ul><li><a href=\"https:\/\/holypython.com\/python-sqlite-tutorial\/sqlite-database-basics-with-db-browser-for-sqlite\/\">DB Browser for SQLite for (manual method)<\/a><\/li><li><a href=\"https:\/\/holypython.com\/python-sqlite-tutorial\/building-a-database-with-sql-python-sqlite\/\">Building a database (Python method)<\/a><\/li><\/ul>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-987e8d5 elementor-widget elementor-widget-menu-anchor\" data-id=\"987e8d5\" data-element_type=\"widget\" data-widget_type=\"menu-anchor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-menu-anchor\" id=\"database_connection\"><\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<section class=\"elementor-section elementor-inner-section elementor-element elementor-element-ffd6d7c elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"ffd6d7c\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-inner-column elementor-element elementor-element-92d1c46\" data-id=\"92d1c46\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-fb52716 elementor-widget elementor-widget-heading\" data-id=\"fb52716\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Connecting to Database and Creating Cursor<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-2dfe85c elementor-widget elementor-widget-text-editor\" data-id=\"2dfe85c\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>First we need to connect to the database and create a cursor with that connection object. We will use this cursor to &#8220;<b>fetch<\/b>&#8221; data from database.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-b73accc elementor-widget elementor-widget-html\" data-id=\"b73accc\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<pre rel=\"Python\"><code>import sqlite3\n\nconn = sqlite3.connect('Desktop\/Family.sqlite3')\ncurr = conn.cursor()\n<\/pre><\/code>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-inner-section elementor-element elementor-element-6ecddfe elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"6ecddfe\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-inner-column elementor-element elementor-element-925a06d\" data-id=\"925a06d\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-3f1bc7c elementor-widget elementor-widget-menu-anchor\" data-id=\"3f1bc7c\" data-element_type=\"widget\" data-widget_type=\"menu-anchor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-menu-anchor\" id=\"fetchone\"><\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-e20ca90 elementor-widget elementor-widget-heading\" data-id=\"e20ca90\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Fetching Database Rows in Python (Fetchone)<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-f408343 elementor-widget elementor-widget-text-editor\" data-id=\"f408343\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Fetching simply means retrieving data in this context. We can use <b>fetchall<\/b> or <b>fetchone<\/b> methods on our cursor. fetchall fetches all rows returned by the SQL query while fetchone returns only one row.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-a9c6565 elementor-widget elementor-widget-html\" data-id=\"a9c6565\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<pre rel=\"Python\"><code>data = cur.execute('SELECT * FROM Fam')\n\nprint(data.fetchone())\n<\/pre><\/code>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-404b983 elementor-widget elementor-widget-text-editor\" data-id=\"404b983\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<pre>('Pam', 50, 'Unknown', None)<\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-inner-section elementor-element elementor-element-4f0e739 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"4f0e739\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-inner-column elementor-element elementor-element-0b506c5\" data-id=\"0b506c5\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-ae50765 elementor-widget elementor-widget-menu-anchor\" data-id=\"ae50765\" data-element_type=\"widget\" data-widget_type=\"menu-anchor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-menu-anchor\" id=\"fetchall\"><\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-449b3f7 elementor-widget elementor-widget-heading\" data-id=\"449b3f7\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Fetching Database Using Fetchall<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-ac1c8f2 elementor-widget elementor-widget-text-editor\" data-id=\"ac1c8f2\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>And fetchall will fetch the whole result of the query. See the example:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-b2391cb elementor-widget elementor-widget-html\" data-id=\"b2391cb\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<pre rel=\"Python\"><code>data = cur.execute('SELECT * FROM Fam')\n\nprint(data.fetchall())\n<\/pre><\/code>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-89d98af elementor-widget elementor-widget-text-editor\" data-id=\"89d98af\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<pre>[('Pam', 50, 'Unknown', None), ('Miranda', 32, 'Unknown', None), <br \/>('Pascal', 45, 'Unknown', None), ('Dave', 12, 'Unknown', None), <br \/>('Emmy', 23, 'Unknown', None), ('Michael', 36, 'Unknown', None)]<\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-0d05fff elementor-widget elementor-widget-text-editor\" data-id=\"0d05fff\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>When you are done querying don&#8217;t forget to close the connection so database doesn&#8217;t remain in use by Python.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-927a716 elementor-widget elementor-widget-html\" data-id=\"927a716\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<pre rel=\"Python\"><code>conn.close()\n<\/pre><\/code>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-0fef586 elementor-widget elementor-widget-text-editor\" data-id=\"0fef586\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>It may seem too simple or trivial but once you master up to here, it doesn&#8217;t matter if database has 1 million rows or 1 billion rows you can tackle it with the same skills.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-inner-section elementor-element elementor-element-28a5632 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"28a5632\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-inner-column elementor-element elementor-element-2640fa2\" data-id=\"2640fa2\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-53f923d elementor-widget elementor-widget-menu-anchor\" data-id=\"53f923d\" data-element_type=\"widget\" data-widget_type=\"menu-anchor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-menu-anchor\" id=\"querying_with_python\"><\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-d34bf1b elementor-widget elementor-widget-heading\" data-id=\"d34bf1b\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Database Querying Example with Python<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-3db071f elementor-widget elementor-widget-html\" data-id=\"3db071f\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<pre rel=\"Python\"><code>import sqlite3\n\nconn = sqlite3.connect('Desktop\/GA3.sqlite')\ncur = conn.cursor()\n\ndata = cur.execute('SELECT * FROM Intuse WHERE Population > 150000000', )\n\noutput = data.fetchall()\nprint(*output, sep=\"\\n\")\n<\/pre><\/code>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-6fdaae5 elementor-widget elementor-widget-text-editor\" data-id=\"6fdaae5\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Here we are getting all the rows where countries have population higher than 150 million.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-815daa8 elementor-widget elementor-widget-text-editor\" data-id=\"815daa8\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<pre class=\"default s-code-block hljs bash\">('China', 904080566.0, 1427647786, 1, '63.33%', 98)<br \/>('India', 755820000.0, 1366417754, 2, '55.40%', 117)<br \/>('United States', 312320000.0, 324459463, 3, '96.26%', 14)<br \/>('Indonesia', 196714070.0, 266911900, 4, '73.70%', 73)<br \/>('Brazil', 150410801.0, 209288278, 6, '71.86%', 75)<br \/>('Nigeria', 136203231.0, 205886311, 7, '66.44%', 89)<br \/>('Bangladesh', 111875000.0, 164945471, 8, '67.79%', 99)<br \/>('Pakistan', 93000000.0, 220892341, 5, '43.50%', 135)<\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-9ec5fc6 elementor-widget elementor-widget-text-editor\" data-id=\"9ec5fc6\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>To see the columns of this table we can use this query.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-ef7105d elementor-widget elementor-widget-html\" data-id=\"ef7105d\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<pre rel=\"Python\"><code>\ndata = cur.execute('PRAGMA table_info(Intuse)')\n\noutput = data.fetchall()\nprint(*output, sep=\"\\n\")\n<\/pre><\/code>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-c1bcc8f elementor-widget elementor-widget-text-editor\" data-id=\"c1bcc8f\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<pre class=\"default s-code-block hljs bash\">(0, 'country', 'TEXT', 0, None, 0)<br \/>(1, 'users', 'REAL', 0, None, 0)<br \/>(2, 'population', 'INT', 0, None, 0)<br \/>(3, 'rank1', 'INT', 0, None, 0)<br \/>(4, 'percentage', 'INT', 0, None, 0)<br \/>(5, 'rank2', 'INT', 0, None, 0)<\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-a679d25 elementor-widget elementor-widget-text-editor\" data-id=\"a679d25\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Now data makes more sense, we can see the internet usage percentage and population of these countries. Based on this table the US seems to have a pretty high internet usage ratio with 96% and Pakistan&#8217;s ratio is pretty low with 43%. Let&#8217;s dig deeper.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-6a3d4b5 elementor-widget elementor-widget-html\" data-id=\"6a3d4b5\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<pre rel=\"Python\"><code>\ndata = cur.execute('SELECT * FROM Intuse order by percentage\n                desc limit 5')\n\noutput = data.fetchall()\nprint(*output, sep=\"\\n\")\n<\/pre><\/code>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-5506fc3 elementor-widget elementor-widget-text-editor\" data-id=\"5506fc3\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<pre class=\"default s-code-block hljs bash\"><br \/>('Falkland Islands', 2881.0, 2910, 213, '99.02%', 1)<br \/>('Andorra', 76095.0, 76965, 194, '98.87%', 2)<br \/>('Bermuda', 60349.0, 61349, 197, '98.37%', 3)<br \/>('Iceland', 329196.0, 335025, 175, '98.26%', 4)<br \/>('Liechtenstein', 37201.0, 37922, 203, '98.10%', 5)<\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-db9798e elementor-widget elementor-widget-text-editor\" data-id=\"db9798e\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>This is really cool to have such power and tweaking sensitivity on queries. Now, we&#8217;re seeing top 5 countries (achieved by <b>LIMIT 5<\/b>) in descending order based on internet usage percentage. Iceland, Bermuda, Liechtenstein, Andorra and Falkland Islands all seem to be doing great.<\/p><p>Interestingly some of these are island countries and internet seems to be providing a great opportunity to offset some of the effects of isolation from the rest of the world. Others are tiny European countries.<\/p><p>Small countries and economies can have unique dynamics for their infrastructure. Let&#8217;s try to discover something similar but for countries above certain size limit.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-f4b5d5b elementor-widget elementor-widget-html\" data-id=\"f4b5d5b\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<pre rel=\"Python\"><code>\ndata = cur.execute('SELECT * FROM Intuse order by percentage\n                desc limit 5')\n\noutput = data.fetchall()\nprint(*output, sep=\"\\n\")\n<\/pre><\/code>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-7c991c8 elementor-widget elementor-widget-text-editor\" data-id=\"7c991c8\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Countries above 1 million population ordered by internet usage:<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-03ef257 elementor-widget elementor-widget-text-editor\" data-id=\"03ef257\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<pre class=\"default s-code-block hljs bash\">('Kuwait', 4053797.0, 4136528, 129, '98.00%', 6)<br \/>('Norway', 5120225.0, 5305383, 117, '96.51%', 12)<br \/>('Sweden', 9554907.0, 9910701, 88, '96.41%', 13)<br \/>('United States', 312320000.0, 324459463, 3, '96.26%', 14)<br \/>('Qatar', 2532059.0, 2639211, 141, '95.94%', 14)<\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-c999b4f elementor-widget elementor-widget-text-editor\" data-id=\"c999b4f\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Gulf countries seem to be doing so well. Obviously it seems internet penetration on that top percentile probably requires lots of investment power. And we also see Nordic countries thriving in this list. Let&#8217;s check the same stats for countries above 10 million population, we&#8217;re entering the big league.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-d332413 elementor-widget elementor-widget-html\" data-id=\"d332413\" data-element_type=\"widget\" data-widget_type=\"html.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<pre rel=\"Python\"><code>\ndata = cur.execute('SELECT * FROM Intuse order by percentage\n                desc limit 5')\n\noutput = data.fetchall()\nprint(*output, sep=\"\\n\")\n<\/pre><\/code>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-edc0a94 elementor-widget elementor-widget-text-editor\" data-id=\"edc0a94\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>Another interesting result from our query. This is really addictive to be able to change one character and have query results exactly as you want from the luxury of your Python console. SQLite and databases offer so much potential for any type of coder really.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-3ec4669 elementor-widget elementor-widget-text-editor\" data-id=\"3ec4669\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<pre class=\"default s-code-block hljs bash\">('United States', 312320000.0, 324459463, 3, '96.26%', 14)<br \/>('South Korea', 49421084.0, 50982212, 27, '95.10%', 16)<br \/>('United Kingdom', 65001016.0, 66181585, 21, '94.62%', 19)<br \/>('Iran', 78086663.0, 83020323, 17, '94.05%', 104)<br \/>('Netherlands', 15877494.0, 17035938, 66, '93.20%', 22)<\/pre>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-687f915 elementor-widget elementor-widget-text-editor\" data-id=\"687f915\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>You can also <a href=\"https:\/\/holypython.com\/python-sqlite-tutorial\/sql-native-visualization\/\">visualize these query results<\/a> on the go using DB Browser for SQLite.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<div class=\"elementor-element elementor-element-acd0f14 elementor-widget elementor-widget-menu-anchor\" data-id=\"acd0f14\" data-element_type=\"widget\" data-widget_type=\"menu-anchor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-menu-anchor\" id=\"sum\"><\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<section class=\"elementor-section elementor-inner-section elementor-element elementor-element-5d7e62e elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"5d7e62e\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-inner-column elementor-element elementor-element-b9ad2c7\" data-id=\"b9ad2c7\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-c10a584 elementor-widget elementor-widget-heading\" data-id=\"c10a584\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Summary<\/h2>\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-7b892c0 elementor-widget elementor-widget-text-editor\" data-id=\"7b892c0\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p>In this SQL tutorial with Python we have seen different SQL queries and how to execute them from inside a Python code using sqlite3 library.<\/p><p>We&#8217;ve seen Python examples for getting database column names and features, creating database connection and cursor, fetching data from SQLite database and more SQL queries on the database with Python.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>SQLite Database Basics with DB Browser for SQLite INSERT INTO &amp; UPDATE More SQL Syntax: SQL Querying: Basics Querying: LIKE Operator Building a Database with Python (SQLite) Querying a Database with Python (SQLite) More SQLite Examples: Python Magic for SQL SQL Native Visualization Python Tutorials Contents Introduction Connecting to database and creating cursor Fetching Database [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":20648,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"full-width.php","meta":{"_acf_changed":false,"footnotes":""},"class_list":["post-21152","page","type-page","status-publish","hentry"],"acf":[],"_links":{"self":[{"href":"https:\/\/holypython.com\/wp-json\/wp\/v2\/pages\/21152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/holypython.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/holypython.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/holypython.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/holypython.com\/wp-json\/wp\/v2\/comments?post=21152"}],"version-history":[{"count":0,"href":"https:\/\/holypython.com\/wp-json\/wp\/v2\/pages\/21152\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/holypython.com\/wp-json\/wp\/v2\/pages\/20648"}],"wp:attachment":[{"href":"https:\/\/holypython.com\/wp-json\/wp\/v2\/media?parent=21152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}