{"id":8528,"date":"2021-02-11T08:48:55","date_gmt":"2021-02-11T07:48:55","guid":{"rendered":"https:\/\/pythonprogramming.altervista.org\/?p=8528"},"modified":"2021-02-11T12:41:09","modified_gmt":"2021-02-11T11:41:09","slug":"sqlite3-and-python-how-to-database-part-1","status":"publish","type":"post","link":"https:\/\/pythonprogramming.altervista.org\/sqlite3-and-python-how-to-database-part-1\/","title":{"rendered":"Sqlite3 and Python &#8211; How to &#8230; Database &#8211; Part 1"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Another series of tutorial about sqlite3 with Python. Let&#8217;s take a look at this new prject.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Databases are really important, useful and make your work much easier, so it is very important to understand how to use them. The little effort that will take to learn to use them will be rewarned.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This time we are going to:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>create a database<\/li><li>create a table<\/li><li>write some data<\/li><li>retrieve the data<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>app.table(&#8220;tablename&#8221;)<\/td><td>create a table<\/td><\/tr><tr><td>app.add_row(&#8220;tablename&#8221;, data)<br>*data is a tuple with all the data<\/td><td>insert data in a row<\/td><\/tr><\/tbody><\/table><figcaption>How to create a new table and insert data into it<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td>app.query()<\/td><td>Look for data<\/td><\/tr><tr><td><\/td><td><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Create a database<\/h2>\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=\"\">    def open(self):\n        self.conn = sqlite3.connect(\"database.db\")\n        self.c = self.conn.cursor()\n        print(self.conn)<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This code will create a database or open an existing one, if it was created yet.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The cursor object is needed before the execute command.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Create a table<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This command is dome with the <strong>table <\/strong>method.<\/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=\"\">        self.c.execute(f'''CREATE TABLE {name}(\n                     date text,\n                     account text,\n                     debit real,\n                     credit real,\n                     diff real\n        )''')<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Insert data in a table<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This command is executed with the <strong>add_row <\/strong>method<\/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=\"\">self.c.execute(f\"INSERT INTO {tablename} VALUES {data}\")<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Retrieve data<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Use the <strong>query <\/strong>method for this.<\/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=\"\">            for row in self.c.execute(f'SELECT * FROM {tablename} ORDER BY {column}'):\n                print(row)<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/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\nimport os\n\nclass Database:\n    def open(self):\n        self.conn = sqlite3.connect(\"database.db\")\n        self.c = self.conn.cursor()\n        print(self.conn)\n\n    def close(self):\n        self.conn.close()\n\n    def table(self, name):\n        \"Create a table\"\n        self.c.execute(f'''CREATE TABLE {name}(\n                     date text,\n                     account text,\n                     debit real,\n                     credit real,\n                     diff real\n        )''')\n\n    def add_row(self, tablename, data):\n        \" Insert a row of data\"\n        self.c.execute(f\"INSERT INTO {tablename} VALUES {data}\")\n\n    def query(self, tablename, column=\"\"):\n        print(column)\n        if column == \"\":\n            for row in self.c.execute(f'SELECT * FROM {tablename}'):\n                print(row)\n        else:\n            for row in self.c.execute(f'SELECT * FROM {tablename} ORDER BY {column}'):\n                print(row)\n\n    def commit(self):\n        self.conn.commit()\n\n\n\napp = Database()\napp.open()\n\ndef create_table():\n    app.table(\"journal\")\n\ndef day_1():\n    \"2021.02.01\"\n    app.add_row(\"journal\", ('2021-02-01','Cash',100,0,100))\n    app.add_row(\"journal\", ('2021-02-01','Cash',200,0,200))\n    app.add_row(\"journal\", ('2021-02-01','Cash',0,50,-50))\n    app.commit()\n\n# day_1()\napp.query(\"journal\", \"diff\")\napp.close()\n\n\n# os.startfile(\".\")<\/pre>\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=\"\">&lt;sqlite3.Connection object at 0x00000257238375D0>\ndiff\n('2021-02-01', 'Cash', 0.0, 50.0, -50.0)\n('2021-02-01', 'Cash', 100.0, 0.0, 100.0)\n('2021-02-01', 'Cash', 200.0, 0.0, 200.0)\n>>> <\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The one above is the output of the code<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/pythonprogramming.altervista.org\/wp-content\/uploads\/2021\/02\/image-21.png\"><img loading=\"lazy\" decoding=\"async\" width=\"800\" height=\"600\" src=\"https:\/\/pythonprogramming.altervista.org\/wp-content\/uploads\/2021\/02\/image-21.png\" alt=\"\" class=\"wp-image-8533\" srcset=\"https:\/\/pythonprogramming.altervista.org\/wp-content\/uploads\/2021\/02\/image-21.png 800w, https:\/\/pythonprogramming.altervista.org\/wp-content\/uploads\/2021\/02\/image-21-320x240.png 320w, https:\/\/pythonprogramming.altervista.org\/wp-content\/uploads\/2021\/02\/image-21-768x576.png 768w\" sizes=\"auto, (max-width: 800px) 100vw, 800px\" \/><\/a><figcaption>Explanation of the methods used to insert the data<\/figcaption><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>first I create an istance of the Database class that contains the methods to do everything (app = Database)<\/li><li>then I open \/ create a database with the <strong>open <\/strong>method (app.open())<\/li><li>then I create a table with the table method<\/li><li>then I insert 3 rows of data with the row method<\/li><li>then I commit the changes, otherwise they won&#8217;t be saved<\/li><li>then I make a search into the journal table for data ordered by the values in diff column<\/li><li>finally I close the database<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">If you look into the methods, you will see how I made it work with sqlite3<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"Another series of tutorial about sqlite3 with Python. Let&#8217;s take a look at this new prject. Databases are really important, useful and make \n<a class=\"moretag\" href=\"https:\/\/pythonprogramming.altervista.org\/sqlite3-and-python-how-to-database-part-1\/\"> [...]<\/a>","protected":false},"author":1,"featured_media":8551,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-8528","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-examples"],"avopt_banners_inside_post":true,"avopt_banners_on_page":true,"av_copy_from":"","av_sharing_message":"","av_sharing_allowed":true,"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\/8528","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=8528"}],"version-history":[{"count":9,"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/posts\/8528\/revisions"}],"predecessor-version":[{"id":8620,"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/posts\/8528\/revisions\/8620"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/media\/8551"}],"wp:attachment":[{"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/media?parent=8528"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/categories?post=8528"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pythonprogramming.altervista.org\/wp-json\/wp\/v2\/tags?post=8528"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}