{"id":4784,"date":"2024-12-11T15:47:20","date_gmt":"2024-12-11T15:47:20","guid":{"rendered":"https:\/\/ibex.tech\/python\/?p=4784"},"modified":"2024-12-11T16:11:02","modified_gmt":"2024-12-11T16:11:02","slug":"settings-table","status":"publish","type":"post","link":"https:\/\/ibex.tech\/python\/database\/sqlite\/examples\/settings-table","title":{"rendered":"Settings table"},"content":{"rendered":"\n<h5 class=\"wp-block-heading\">Create the table on startup<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>    global db1\n    global db1cursor\n    \n    #Open the database (will be created if not already present)\n    db1 = sqlite3.connect(Path(ap_defines.DIRECTORY_FILES_ROOT + ap_defines.DIRECTORY_FILES_DATABASE + ap_defines.FILENAME_DATABASE_MY_DB_NAME))\n    \n    db1cursor = db1.cursor()\n    \n    #-------------------------------------\n    #----- CREATE TABLES IF REQUIRED -----\n    #-------------------------------------\n  \n    #tbl_settings\n    #General app settings\n    db1cursor.execute(\"\"\"CREATE TABLE IF NOT EXISTS tbl_settings (\n                        setting_name TEXT NOT NULL PRIMARY KEY,\n                        setting_value TEXT\n        )\"\"\")\n    \n\n    #&lt;&lt;&lt;Add any other tables here\n\n\n    db1.commit()<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">Accessing the table<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>\n#**********************************************\n#**********************************************\n#********** SETTINGS - WRITE SETTING **********\n#**********************************************\n#**********************************************\ndef settings_write_setting(setting_name, setting_value):\n    \n    \n    db1cursor.execute(\"\"\"INSERT INTO tbl_settings (\n                        setting_name,\n                        setting_value\n                    ) VALUES (\n                        ?,\n                        ?\n                    )\n                      ON CONFLICT(setting_name) DO UPDATE SET setting_value=?;\n                    \"\"\",\n                    (setting_name, setting_value, setting_value));\n    db1.commit()\n    #if db1cursor.rowcount &lt; 0:\n    #    print(\"settings_write_setting FAILED\");\n    \n    \n#*********************************************\n#*********************************************\n#********** SETTINGS - READ SETTING **********\n#*********************************************\n#*********************************************\n#Returns setting_value string, or \"\" if not found\ndef settings_read_setting(setting_name):\n    \n    db1cursor.execute(\"\"\"SELECT setting_value FROM tbl_settings \n                      WHERE setting_name = ?\n                      \"\"\",(setting_name,))\n    row = db1cursor.fetchone()\n    if row != None:\n        setting_value = row&#91;0]\n    else:\n        setting_value = \"\"\n    \n    return(setting_value)\n\n    \n\n\n#***********************************************\n#***********************************************\n#********** SETTINGS - DELETE SETTING **********\n#***********************************************\n#***********************************************\ndef settings_delete_setting(setting_name):\n    \n    db1cursor.execute(\"\"\"DELETE FROM tbl_settings \n                      WHERE setting_name = ?\n                      \"\"\",(setting_name,))\n    db1.commit()\n<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">Testing its operation<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>    ap_database.settings_write_setting(\"MySetting1\", \"abcd\")\n    \n    setting_value = ap_database.settings_read_setting(\"MySetting1\")\n    print(\"settings_read_setting 1: \" + setting_value)\n    \n    ap_database.settings_delete_setting(\"MySetting1\")\n\n    setting_value = ap_database.settings_read_setting(\"MySetting1\")\n    print(\"settings_read_setting 2: \" + setting_value)\n    if (setting_value == \"\"):\n        print(\"Setting was deleted\")<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Create the table on startup Accessing the table Testing its operation<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[378],"tags":[],"class_list":["post-4784","post","type-post","status-publish","format-standard","hentry","category-examples"],"_links":{"self":[{"href":"https:\/\/ibex.tech\/python\/wp-json\/wp\/v2\/posts\/4784","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ibex.tech\/python\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ibex.tech\/python\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ibex.tech\/python\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/ibex.tech\/python\/wp-json\/wp\/v2\/comments?post=4784"}],"version-history":[{"count":2,"href":"https:\/\/ibex.tech\/python\/wp-json\/wp\/v2\/posts\/4784\/revisions"}],"predecessor-version":[{"id":4786,"href":"https:\/\/ibex.tech\/python\/wp-json\/wp\/v2\/posts\/4784\/revisions\/4786"}],"wp:attachment":[{"href":"https:\/\/ibex.tech\/python\/wp-json\/wp\/v2\/media?parent=4784"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ibex.tech\/python\/wp-json\/wp\/v2\/categories?post=4784"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ibex.tech\/python\/wp-json\/wp\/v2\/tags?post=4784"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}