{"id":54,"date":"2016-01-10T20:55:26","date_gmt":"2016-01-10T20:55:26","guid":{"rendered":"http:\/\/mr-west.uk\/sql\/?page_id=54"},"modified":"2017-03-31T10:19:25","modified_gmt":"2017-03-31T10:19:25","slug":"sql-and-python","status":"publish","type":"page","link":"https:\/\/learnlearn.uk\/sql\/sql-and-python\/","title":{"rendered":"SQL and Python"},"content":{"rendered":"\n<div class=\"arconix-tabs-horizontal\"><ul class=\"arconix-tabs\"><li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Starter\"><a class=\"\" href=\"#tab-Starter\">Starter<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Connectingtoadatabase\"><a class=\"\" href=\"#tab-Connectingtoadatabase\">Connecting to a database<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Insert\"><a class=\"\" href=\"#tab-Insert\">Insert<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Select\"><a class=\"\" href=\"#tab-Select\">Select<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Update\"><a class=\"\" href=\"#tab-Update\">Update<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Delete\"><a class=\"\" href=\"#tab-Delete\">Delete<\/a><\/li>\n<li data-arconix-icon=\" \" data-arconix-color=\" \" class=\"arconix-tab tab-Resources\"><a class=\"\" href=\"#tab-Resources\">Resources<\/a><\/li><\/ul>\n<div class=\"arconix-panes\"><div class=\"arconix-pane pane-Starter\">\n<\/div>\n<div class=\"arconix-pane pane-Connectingtoadatabase\">\n<p>Connecting to an SQLite database in Python is easy, because it is simply a file.<\/p>\n<code><\/p>\n<p>import sqlite3 # Import the module<br \/>\nconn = sqlite3.connect('example.db') # Connect to the database.<br \/>\nc = conn.cursor()<\/p>\n<p><\/code>\n<div class=\"arconix-box arconix-box-lgreen\"><i class='fa fa-2x pull-left fa-info'><\/i><div class=\"arconix-box-content\">\n<p><strong>Why do I need a cursor?<\/strong><\/p>\n<p>So far we have been executing SQL queries right from inside SQL itself and every command we wanted to execute, we did so using a cursor, the flashing one on the screen! In Python we are not typing our commands directly, so we have to create a virtual cursor and Python will &#8216;type&#8217; in to SQL on our behalf.<\/p>\n<\/div><\/div>\n<\/div>\n<div class=\"arconix-pane pane-Insert\">\n<h3>entry_tuple = (<span style=\"color: #0000ff;\">col1_data<\/span>, <span style=\"color: #0000ff;\">col2_data<\/span>, <span style=\"color: #0000ff;\">col3_data<\/span>, <span style=\"color: #0000ff;\">col4_data<\/span>)<\/h3>\n<h3>c.execute(&#8216;INSERT INTO<span style=\"color: #0000ff;\"> table_name<\/span>\u00a0VALUES (?,?,?,?)&#8217;, entry_tuple)<\/h3>\n<p>&nbsp;<\/p>\n<p>Adding data to an SQLite database is easy in Python, we simply execute gather the data we want to add and then execute an SQL statement as normal.<\/p>\n<code><\/p>\n<p>entry = (\"Bob\",\"Jones\",\"01523 2233434\",\"bob@jones.com\")<\/p>\n<p>c.execute('INSERT INTO stocks VALUES (?,?,?,?)', entry)<\/p>\n<p><\/code>\n<div class=\"arconix-box arconix-box-lgreen\"><i class='fa fa-2x pull-left fa-info'><\/i><div class=\"arconix-box-content\">\n<p><strong>Caution!<\/strong><\/p>\n<p>Take care that the number of items in your tuple are the same as the number of Question Marks in your query.<\/p>\n<p>Also if you don&#8217;t want to specify the contents of every column in the table (e.g. you don&#8217;t know a person&#8217;s email address or you have set a primary key columnn that is auto incrementing) then you will have to use the slightly longer version of the query where you specify the column names that you will be adding to.<\/p>\n<pre><strong>Example<\/strong>\r\nc.execute('INSERT INTO stocks (firstname,lastname,telephone,email) VALUES (?,?,?,?)', entry)<\/pre>\n<\/div><\/div>\n<h3>Why do we need to use the question marks???<\/h3>\n<p>Using the ? marks instead of using string substitution helps protect your Python program from sql injection attacks, as it sanitises the input data first.<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\"alignnone\" src=\"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/exploits_of_a_mom.png\" alt=\"\" width=\"666\" height=\"205\" \/><\/p>\n<p><em>Creative commons \u00a0&#8211; Source:\u00a0http:\/\/xkcd.com\/<\/em><\/p>\n<p>&nbsp;<\/p>\n<\/div>\n<div class=\"arconix-pane pane-Select\">\n<h3>for row in c.execute(&#8216;SELECT * FROM <span style=\"color: #0000ff;\">table_name<\/span>&#8216;):<\/h3>\n<h3>\u00a0 \u00a0 print(row) #or do something else here<\/h3>\n<p>&nbsp;<\/p>\n<p>If we want to display the contents of a table, we need to use the select query. The select query returns a queryset object that we can combine with a for loop to print out the results(or do other things).<\/p>\n<code><\/p>\n<p>search_tuple = (\"bob\",\"jones\")<\/p>\n<p>rows = c.execute('SELECT * FROM contacts&amp;nbsp;WHERE firstname=?, lastname = ? ', search_tuple)<\/p>\n<p>for row in rows:<\/p>\n<p>print(row)<\/p>\n<p><\/code>\n<\/div>\n<div class=\"arconix-pane pane-Update\">\n<h3>c.execute(&#8216;UPDATE <span style=\"color: #0000ff;\">table_name<\/span> SET <span style=\"color: #0000ff;\">column_name<\/span> = ? WHERE <span style=\"color: #0000ff;\">column_name<\/span> =?&#8217;, entry_tuple)<\/h3>\n<code><\/p>\n<p>entry_tuple = (\"bob@jones.com\", 45)<\/p>\n<p>c.execute('UPDATE contact\u00a0SET email\u00a0= ? WHERE contactId =\u00a0\u00a0?', entry_tuple)<\/p>\n<p><\/code>\n<div class=\"arconix-box arconix-box-lgreen\"><i class='fa fa-2x pull-left fa-info'><\/i><div class=\"arconix-box-content\">\n<p><strong>Helpful hint!<\/strong><\/p>\n<p>The safest and most effective way of updating entries within a table is to set up an auto incrementing primary key and then use that reference when developing your programs. This avoids accidentally updating the wrong entry(for example if two people shared the same name!).<\/p>\n<\/div><\/div>\n<\/div>\n<div class=\"arconix-pane pane-Delete\">\n<\/div>\n<div class=\"arconix-pane pane-Resources\">\n<\/div><\/div><\/div>\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":""},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQL and Python - SQL<\/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:\/\/learnlearn.uk\/sql\/sql-and-python\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL and Python - SQL\" \/>\n<meta property=\"og:url\" content=\"https:\/\/learnlearn.uk\/sql\/sql-and-python\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL\" \/>\n<meta property=\"article:modified_time\" content=\"2017-03-31T10:19:25+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/exploits_of_a_mom.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/sql-and-python\/\",\"url\":\"https:\/\/learnlearn.uk\/sql\/sql-and-python\/\",\"name\":\"SQL and Python - SQL\",\"isPartOf\":{\"@id\":\"https:\/\/learnlearn.uk\/sql\/#website\"},\"datePublished\":\"2016-01-10T20:55:26+00:00\",\"dateModified\":\"2017-03-31T10:19:25+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/learnlearn.uk\/sql\/sql-and-python\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/learnlearn.uk\/sql\/sql-and-python\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/sql-and-python\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/learnlearn.uk\/sql\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL and Python\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/#website\",\"url\":\"https:\/\/learnlearn.uk\/sql\/\",\"name\":\"SQL\",\"description\":\"Databases\",\"publisher\":{\"@id\":\"https:\/\/learnlearn.uk\/sql\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/learnlearn.uk\/sql\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/#organization\",\"name\":\"SQL\",\"url\":\"https:\/\/learnlearn.uk\/sql\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/learnlearn.uk\/sql\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2019\/02\/LearnLearnLogowhite.png\",\"contentUrl\":\"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2019\/02\/LearnLearnLogowhite.png\",\"width\":710,\"height\":98,\"caption\":\"SQL\"},\"image\":{\"@id\":\"https:\/\/learnlearn.uk\/sql\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL and Python - SQL","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/learnlearn.uk\/sql\/sql-and-python\/","og_locale":"en_GB","og_type":"article","og_title":"SQL and Python - SQL","og_url":"https:\/\/learnlearn.uk\/sql\/sql-and-python\/","og_site_name":"SQL","article_modified_time":"2017-03-31T10:19:25+00:00","og_image":[{"url":"http:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2016\/01\/exploits_of_a_mom.png"}],"twitter_card":"summary_large_image","twitter_misc":{"Estimated reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/learnlearn.uk\/sql\/sql-and-python\/","url":"https:\/\/learnlearn.uk\/sql\/sql-and-python\/","name":"SQL and Python - SQL","isPartOf":{"@id":"https:\/\/learnlearn.uk\/sql\/#website"},"datePublished":"2016-01-10T20:55:26+00:00","dateModified":"2017-03-31T10:19:25+00:00","breadcrumb":{"@id":"https:\/\/learnlearn.uk\/sql\/sql-and-python\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/learnlearn.uk\/sql\/sql-and-python\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/learnlearn.uk\/sql\/sql-and-python\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/learnlearn.uk\/sql\/"},{"@type":"ListItem","position":2,"name":"SQL and Python"}]},{"@type":"WebSite","@id":"https:\/\/learnlearn.uk\/sql\/#website","url":"https:\/\/learnlearn.uk\/sql\/","name":"SQL","description":"Databases","publisher":{"@id":"https:\/\/learnlearn.uk\/sql\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/learnlearn.uk\/sql\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-GB"},{"@type":"Organization","@id":"https:\/\/learnlearn.uk\/sql\/#organization","name":"SQL","url":"https:\/\/learnlearn.uk\/sql\/","logo":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/learnlearn.uk\/sql\/#\/schema\/logo\/image\/","url":"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2019\/02\/LearnLearnLogowhite.png","contentUrl":"https:\/\/learnlearn.uk\/sql\/wp-content\/uploads\/sites\/6\/2019\/02\/LearnLearnLogowhite.png","width":710,"height":98,"caption":"SQL"},"image":{"@id":"https:\/\/learnlearn.uk\/sql\/#\/schema\/logo\/image\/"}}]}},"rttpg_featured_image_url":null,"rttpg_author":{"display_name":"learnlearnadmin","author_link":"https:\/\/learnlearn.uk\/sql\/author\/learnlearnadmin\/"},"rttpg_comment":0,"rttpg_category":null,"rttpg_excerpt":null,"_links":{"self":[{"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/pages\/54"}],"collection":[{"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/comments?post=54"}],"version-history":[{"count":0,"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/pages\/54\/revisions"}],"wp:attachment":[{"href":"https:\/\/learnlearn.uk\/sql\/wp-json\/wp\/v2\/media?parent=54"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}