{"id":2619,"date":"2019-08-02T07:24:28","date_gmt":"2019-08-02T05:24:28","guid":{"rendered":"https:\/\/pythonprogramming.altervista.org\/?p=2619"},"modified":"2021-06-18T21:42:44","modified_gmt":"2021-06-18T19:42:44","slug":"python-and-sqlite-with-tkinter-part-1","status":"publish","type":"post","link":"https:\/\/pythonprogramming.altervista.org\/python-and-sqlite-with-tkinter-part-1\/","title":{"rendered":"Python, Sqlite and Tkinter: how to put data into a listbox"},"content":{"rendered":"\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-python-programming wp-block-embed-python-programming\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"V8bJr2y6B6\"><a href=\"https:\/\/pythonprogramming.altervista.org\/sqlite3-and-python-how-to-database-part-1\/\">Sqlite3 and Python &#8211; How to &#8230; Database &#8211; Part 1<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Sqlite3 and Python &#8211; How to &#8230; Database &#8211; Part 1&#8221; &#8212; python programming\" src=\"https:\/\/pythonprogramming.altervista.org\/sqlite3-and-python-how-to-database-part-1\/embed\/#?secret=8SY8n28xTL#?secret=V8bJr2y6B6\" data-secret=\"V8bJr2y6B6\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><figcaption>New post about sqlite with python<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Making database, working visually<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Hi, today we are going to make a <strong>tutorial<\/strong> that will lead you to make a very simple <strong>sqlite<\/strong> <strong>database<\/strong> controlled by <strong>Python<\/strong> with a <strong>GUI<\/strong>, graphic user interface, realized with the built-in module <strong>tkinter<\/strong>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The code<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In this first part, we will create a <strong>GUI<\/strong> to create databases. We will also have a <strong>list<\/strong> of the databases created in the window that we are going to create. You will learn to:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>create a <strong>database<\/strong> with sqlite<\/li><li>create a <strong>window<\/strong> with tkinter<\/li><li>populate the window with <strong>widgets<\/strong>:\n<ul>\n<li>labels<\/li>\n<li>entry<\/li>\n<li>buttons<\/li>\n<li>listbox<\/li>\n<\/ul>\n<\/li><li>add a command to a button to make the computer act on the database<\/li><li>insert values (populate) a listbox with data from the folder<\/li><li>get values from an entry and insert them in the listbox<\/li><li>create a class<\/li><li>access to a method or attribute of the class<\/li><li>and other little skills<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Create a database<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To create a database with sqlite3, after you <strong>imported<\/strong> it<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:default decode:true\">import sqlite3 as lite<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">you do it this way using <strong>connet <\/strong>with the name of the database you want to create if it does not exist yet:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:default decode:true\">conn = lite.connect(\"mydatabase.db\")<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Now the database is created, go to look in the folder where you are coding and you&#8217;ll see the file. Good job!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It is a good behaviour to close the database when you do not use it, like this:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:default decode:true\">conn.close()<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">You could also do use <strong>with<\/strong>:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">filename = \"db1.db\"\nwith lite.connect(filename) as conn:\n    print(f\"I created my database named {filename}\")<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">output:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:default decode:true\">I created my database named db1.db<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This way the db will close automatically and in a cleaner way, without having troubles if the files should be called by another process before it is closed.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We could create a simple function that opens the bd, does something with it and close it:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">import sqlite3 as lite\n\n\ndef db_open(filename):\n    \"Opens and close the database\"\n    with lite.connect(filename) as conn:\n        print(f\"I created my database named {filename}\")\n\n\ndb_open(\"db1.db\")<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This is very neat and it make you use your database and close it just with this simple call of the db_open function passing the name of the database you want to create or open.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Let&#8217;s make a GUI to create the database<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The code below shows how to <strong>create<\/strong> a db. It will be called when you will press a <strong>button<\/strong>.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>you click the button (the code to make the button will be shown after the one below)<\/li><li>the click of the button calls the create function passing the window obj as an argument<\/li><li>the function will get the text (the database name) that you&#8217;ve written into the &#8216;e&#8217; object (a text input widget &#8211; Entry)<\/li><li>it will check if there is the .db extension to this database filename and will add to it if not<\/li><li>it will create the database with that name<\/li><li>it will add the name of the database to a listbox<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">In the GUI we will build, in fact, there will be an entry where the user will write the name of the database and when he presses the button it sends all the window object (<strong>window<\/strong>) to this <strong>function<\/strong> below so that we can take the name of the database with <strong>window.e.get()<\/strong>, where <strong>e<\/strong> is the name of the entry. I will give more explanations after the following code:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">import sqlite3 as lite\nfrom sqlite3 import Error\nimport tkinter as tk\nfrom glob import glob\n\ndef create(window):\n\tdb = window.e.get()\n\n\tif db[-3] == \".db\":\n\t\tpass\n\telse:\n\t\tdb = db + \".db\"\n\ttry:\n\t\tconn = lite.connect(db)\n\t\treturn conn\n\texcept Error as e:\n\t\tprint(e)\n\tfinally:\n\t\tconn.close()\n\t\twindow.lb.insert(tk.END, db)\n\t\twindow.db.set(\"\")\n<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The above <strong>funtion<\/strong> gets from the user (in <strong>db)<\/strong> the text with the name of the database from an <strong>entry<\/strong> (called e) that will be in the <strong>GUI<\/strong> (in the following code below). In practice window is the istance of the GUI class, e is the entry in that GUI and get() is the method to get the text in e. Now that we have it in db, we can check if the user has used the .db extension in the name (in window.e). If not, the code adds the &#8220;.db&#8221; extension at the end.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><a href=\"https:\/\/pythonprogramming.altervista.org\/wp-content\/uploads\/2019\/08\/connect2.png\"><img loading=\"lazy\" decoding=\"async\" width=\"697\" height=\"565\" src=\"https:\/\/pythonprogramming.altervista.org\/wp-content\/uploads\/2019\/08\/connect2.png\" alt=\"\" class=\"wp-image-3310\" srcset=\"https:\/\/pythonprogramming.altervista.org\/wp-content\/uploads\/2019\/08\/connect2.png 697w, https:\/\/pythonprogramming.altervista.org\/wp-content\/uploads\/2019\/08\/connect2-320x259.png 320w\" sizes=\"auto, (max-width: 697px) 100vw, 697px\" \/><\/a><\/figure><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">After this initial checks, the database is created (<strong>conn<\/strong> = <strong>lite<\/strong>.<strong>connect<\/strong>(db)). lite is the module sqlite3 (we gave it that name in the import line, take a look at the 1st line).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">P.S. :With the <strong>try except<\/strong> block we test a block of code for errors. If all goes right it creates the db and&nbsp;<strong>finally<\/strong> (at the end) it closes it, adds the name of the db in lb (the listbox that we will add) and wil delete the name the user has input in the entry (window.db.set).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Now let&#8217;s see the code for the GUI window tha will make everything clearer.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">class Window:\n\t\"\"\"Creates the widgets of the window\"\"\"\n\tdef __init__(self):\n\t\tself.win = tk.Tk()\n\t\tself.label()\n\t\tself.entry()\n\t\tself.button()\n\t\tself.listbox()\n\n\tdef label(self):\n\t\tself.l = tk.Label(self.win, text=\"Create a db [insert the name]\")\n\t\tself.l.pack()\n\n\tdef entry(self):\n\t\tself.db = tk.StringVar()\n\t\tself.e = tk.Entry(self.win, textvariable=self.db)\n\t\tself.e.pack()\n\n\tdef button(self):\n\t\tself.b = tk.Button(self.win, text=\"Create DB\", command= lambda: create(self))\n\t\tself.b.pack()\n\n\tdef listbox(self):\n\t\tself.lb = tk.Listbox(self.win)\n\t\tself.lb.pack()\n\t\tself.show_db()\n\n\tdef show_db(self):\n\t\tfor file in glob(\"*.db\"):\n\t\t\tself.lb.insert(tk.END, file)\n\n\t\t\n\t\tself.win.mainloop()\n\nwin = Window()<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">In this class, that I rewrote differently from the one you see in the video, creates a label, an entry, a button, a listbox. I created a method for each widget to make more readable the code.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When the user hits the <strong>button<\/strong> you see the <strong>command<\/strong> is a lambda function (an anonymous function) that runs the code in the create(db) funtion that we&#8217;ve seen previously.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The whole code<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Now we can take a look at the whole code.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">import sqlite3 as lite\nfrom sqlite3 import Error\nimport tkinter as tk\nfrom glob import glob\n\ndef create(obj):\n\tdb = obj.e.get()\n\n\tif db[-3] == \".db\":\n\t\tpass\n\telse:\n\t\tdb = db + \".db\"\n\ttry:\n\t\tconn = lite.connect(db)\n\t\treturn conn\n\texcept Error as e:\n\t\tprint(e)\n\tfinally:\n\t\tconn.close()\n\t\tobj.lb.insert(tk.END, db)\n\t\tobj.db.set(\"\")\n\nclass Window:\n\t\"\"\"Creates the widgets of the window\"\"\"\n\tdef __init__(self):\n\t\tself.win = tk.Tk()\n\t\tself.label()\n\t\tself.entry()\n\t\tself.button()\n\t\tself.listbox()\n\n\tdef label(self):\n\t\tself.l = tk.Label(self.win, text=\"Create a db [insert the name]\")\n\t\tself.l.pack()\n\n\tdef entry(self):\n\t\tself.db = tk.StringVar()\n\t\tself.e = tk.Entry(self.win, textvariable=self.db)\n\t\tself.e.pack()\n\n\tdef button(self):\n\t\tself.b = tk.Button(self.win, text=\"Create DB\", command= lambda: create(self))\n\t\tself.b.pack()\n\n\tdef listbox(self):\n\t\tself.lb = tk.Listbox(self.win)\n\t\tself.lb.pack()\n\t\tself.show_db()\n\n\tdef show_db(self):\n\t\tfor file in glob(\"*.db\"):\n\t\t\tself.lb.insert(tk.END, file)\n\n\t\t\n\t\tself.win.mainloop()\n\nwin = Window()\n<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Live coding to make a db with sqlite and Python with tkinter<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This video explains the code above to make a database with sqlite3. It lasts 20 minutes.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Python, SqLite and Tkinter [part 1]\" width=\"747\" height=\"420\" src=\"https:\/\/www.youtube.com\/embed\/HTD2y9AYGkI?feature=oembed&amp;enablejsapi=1\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n\n<\/div><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In the next post we will see <a href=\"https:\/\/pythonprogramming.altervista.org\/sqlite3-tkinter-part-ii\/\">how to create a table with the tkinter GUI<\/a>.<\/p>\n\n\n<script>\r\nlet title = \"SqLite3 & Python\"\r\n\tlet links = [\r\n[\"https:\/\/pythonprogramming.altervista.org\/python-and-sqlite-basics\/\",\"Sqlite3 Basic 1\"],\r\n[\"https:\/\/pythonprogramming.altervista.org\/python-and-sqlite-with-tkinter-part-1\/\",\"Sqlite3 - part 1\"],\r\n[\"https:\/\/pythonprogramming.altervista.org\/sqlite3-tkinter-part-ii\/\",\"Sqlite3 - part 2\"],\r\n[\"https:\/\/pythonprogramming.altervista.org\/python-sqlite3-part-3-reoganizing-the-code\/\",\"Sqlite3 - part 3.1\"],\r\n[\"https:\/\/pythonprogramming.altervista.org\/sqlite-and-python-part-3-2\/\",\"Sqlite3 - part 3.2\"],\r\n[\"https:\/\/pythonprogramming.altervista.org\/sqlite-and-tkinter-in-python-part-3-3\/\",\"Sqlite3 - part 3.3\"]\r\n\t];\r\n<\/script>\r\n<script>\r\n\t\r\nif (typeof next2 != \"undefined\"){let next2 = 0;}\r\n\t\r\nnext2 = 0;\r\n\thtml = \"\";\/\/<b style='color:coral;font-size:1.2em'>Other posts about \" + title + \"<\/b><br>\";\r\nfor (address of links) \r\n{\r\n\r\n\tif (next2 == 1){\r\n\t\thtml += \"<div style='background:coral'>\";\r\n\t\thtml += \"Next link => <a href='\" + address[0] + \"'>\" + address[1] + \"<\/a>\";\r\n\t\thtml += \"<\/div><br>\";\r\n\t\tnext2 = 0;\r\n\t}\r\n\tif (address[0] == document.URL) {\r\n\t\tnext2 = 1;\r\n\t}\r\n}\r\n\r\nif (typeof next != \"undefined\") {let next = 0;}\r\nif (typeof addressStart != \"undefined\") {let addressStart = \"\";}\r\nnext = 0;\r\naddressStart = \"<a href='\";\r\nfor (address of links) {\r\n\tif (next == 1){\r\n\t\thtml += \">>>\" + addressStart + address[0] + \"'>\" + address[1] + \"<\/a><br>\";\r\n\t\tnext = 0;\r\n\t}\r\n\telse if (addressStart + address[0] != document.URL)\r\n\t{\r\n\t\thtml += addressStart + address[0] + \"'>\" + address[1] + \"<\/a><br>\";\r\n\t}\r\n\telse\r\n\t{\r\n\t\tnext = 1;\r\n\t\tnext_address = address[0]\r\n\t\tnext_title = address[1]\r\n\t\thtml += \"<span style='color:gray'>\" + address[1] + \"<\/span><br>\";\r\n\t}\r\n\r\n}\r\n\r\n\thtml += `<span style=\"font-size:8px\">Powered by <a href=\"https:\/\/pythonprogramming.altervista.org\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-2673\" src=\"https:\/\/pythonprogramming.altervista.org\/wp-content\/uploads\/2019\/06\/altervista2.png\" alt=\"\" width=\"70\" height=\"25\" srcset=\"https:\/\/pythonprogramming.altervista.org\/wp-content\/uploads\/2019\/06\/altervista2.png 156w, https:\/\/pythonprogramming.altervista.org\/wp-content\/uploads\/2019\/06\/altervista2-150x56.png 150w\" sizes=\"auto, (max-width: 70px) 100vw, 70px\" \/>pythonprogramming.altervista.org<\/a><\/span>`\r\n\thtml = \"<div style='background:yellow'>\" + html + \"<\/div>\";\r\n\tdocument.write(html)\r\n<\/script>\n\n\n\n<h2 class=\"wp-block-heading\">New post and video about sqlite tutorial<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">For you who are interested in Sqlite3 and Python, I got some brand new stuff for your here.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-python-programming wp-block-embed-python-programming\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"V8bJr2y6B6\"><a href=\"https:\/\/pythonprogramming.altervista.org\/sqlite3-and-python-how-to-database-part-1\/\">Sqlite3 and Python &#8211; How to &#8230; Database &#8211; Part 1<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Sqlite3 and Python &#8211; How to &#8230; Database &#8211; Part 1&#8221; &#8212; python programming\" src=\"https:\/\/pythonprogramming.altervista.org\/sqlite3-and-python-how-to-database-part-1\/embed\/#?secret=8SY8n28xTL#?secret=V8bJr2y6B6\" data-secret=\"V8bJr2y6B6\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n","protected":false},"excerpt":{"rendered":"Tkinter and Sqlite3 together united to build our database with some visual help.\n<a class=\"moretag\" href=\"https:\/\/pythonprogramming.altervista.org\/python-and-sqlite-with-tkinter-part-1\/\"> [...]<\/a>","protected":false},"author":1,"featured_media":2620,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[479,483,480,481,484],"tags":[239,4,485,51,486],"class_list":["post-2619","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","category-python","category-sqlite","category-sqlite3","category-tkinter-and-sqlite3","tag-database","tag-python","tag-sqlite3","tag-tkinter","tag-tkinter-and-sqlite3"],"avopt_banners_inside_post":true,"avopt_banners_on_page":true,"av_copy_from":"","av_sharing_message":"","av_sharing_allowed":false,"av_sharing_on":{"fb":[],"tw":[]},"av_allow_affiliate_banner":false,"av_allow_affiliate_multi_banner":false,"av_show_affiliation_buy_button":false,"av_post_rating":true,"av_have_post_rating_value":false,"av_is_artificial_intelligence_content":false,"_links":{"self":[{"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/posts\/2619","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/comments?post=2619"}],"version-history":[{"count":10,"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/posts\/2619\/revisions"}],"predecessor-version":[{"id":9680,"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/posts\/2619\/revisions\/9680"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/media\/2620"}],"wp:attachment":[{"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/media?parent=2619"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/categories?post=2619"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/tags?post=2619"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}