{"id":53,"date":"2023-03-30T04:56:55","date_gmt":"2023-03-30T04:56:55","guid":{"rendered":"http:\/\/www.programminginpython.com\/2023\/03\/30\/how-to-access-mysql-database-in-python\/"},"modified":"2023-04-27T14:21:55","modified_gmt":"2023-04-27T14:21:55","slug":"access-mysql-database-python","status":"publish","type":"post","link":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/","title":{"rendered":"How to perform MySQL database CRUD in Python"},"content":{"rendered":"<p>Hello everyone, welcome back to <a href=\"\/\" target=\"_blank\" rel=\"noopener noreferrer\">programminginpython.com<\/a>. Here I am going to show you how to access MySQL database and perform all database operations on the database in Python. In most of the applications, you need a database to store, update, and manage your data. So here I will show you all the CRUD(Create, Read, Update, Delete) operations which can be performed on a database so basically we are doing a MySQL database CRUD operations in python. So let\u2019s get started.<\/p>\n<div class=\"yrc-shell-cover yrc-single\" data-yrc-uid=\"69e0d9b9e5798\" data-yrc-channel=\"{&quot;meta&quot;:{&quot;user&quot;:&quot;Programming In Python&quot;,&quot;channel&quot;:&quot;UCLBlXUMLYLxopRljNdLXOeQ&quot;,&quot;key&quot;:&quot;yrc_1683138754&quot;,&quot;apikey&quot;:&quot;AIzaSyC3vU6rFyzsO0shrRsAWmjuSiuWYTQhafc&quot;,&quot;cache&quot;:&quot;1440&quot;,&quot;channel_uploads&quot;:&quot;UULBlXUMLYLxopRljNdLXOeQ&quot;,&quot;onlyonce&quot;:&quot;&quot;,&quot;tag&quot;:&quot;&quot;,&quot;per_page&quot;:&quot;24&quot;,&quot;maxv&quot;:&quot;0&quot;,&quot;consent&quot;:{&quot;ask&quot;:&quot;&quot;,&quot;url&quot;:&quot;&quot;},&quot;ads&quot;:&quot;1&quot;,&quot;uid&quot;:&quot;69e0d9b9e5798&quot;,&quot;nocookie&quot;:&quot;&quot;,&quot;single&quot;:true},&quot;style&quot;:{&quot;colors&quot;:{&quot;item&quot;:{&quot;background&quot;:&quot;inherit&quot;},&quot;button&quot;:{&quot;background&quot;:&quot;#333&quot;,&quot;color&quot;:&quot;#fff&quot;},&quot;color&quot;:{&quot;text&quot;:&quot;#fff&quot;,&quot;link&quot;:&quot;inherit&quot;,&quot;menu&quot;:&quot;#000&quot;,&quot;meta&quot;:&quot;inherit&quot;}},&quot;theme&quot;:{&quot;videos&quot;:{&quot;style&quot;:&quot;__grid&quot;,&quot;thumb&quot;:[&quot;large&quot;,&quot;open&quot;],&quot;desc&quot;:&quot;&quot;,&quot;carousel&quot;:{&quot;thumbs&quot;:&quot;4&quot;,&quot;thumbs_to_slide&quot;:&quot;2&quot;,&quot;spacing&quot;:&quot;10&quot;},&quot;carousel_nav&quot;:{&quot;modifier&quot;:&quot;__sides&quot;,&quot;position&quot;:&quot;left-none&quot;,&quot;location&quot;:&quot;prepend&quot;,&quot;background&quot;:&quot;#fff&quot;,&quot;color&quot;:&quot;#000&quot;,&quot;font_size&quot;:&quot;2&quot;,&quot;border_radius&quot;:&quot;0&quot;}},&quot;a&quot;:&quot;1&quot;},&quot;fit&quot;:&quot;false&quot;,&quot;playlists&quot;:&quot;true&quot;,&quot;uploads&quot;:&quot;true&quot;,&quot;player_mode&quot;:&quot;1&quot;,&quot;truncate&quot;:&quot;&quot;,&quot;banner&quot;:&quot;true&quot;,&quot;thumb_margin&quot;:&quot;8&quot;,&quot;play_icon&quot;:&quot;hover&quot;,&quot;youtube_play_icon&quot;:&quot;&quot;,&quot;thumb_image_size&quot;:&quot;medium&quot;,&quot;default_tab&quot;:&quot;uploads&quot;,&quot;sticky&quot;:{&quot;enable&quot;:&quot;&quot;,&quot;width&quot;:&quot;400&quot;,&quot;position&quot;:&quot;bottom-right&quot;,&quot;only_above&quot;:&quot;768&quot;,&quot;margin&quot;:&quot;12&quot;},&quot;player&quot;:{&quot;show_desc&quot;:&quot;&quot;,&quot;show_meta&quot;:&quot;&quot;},&quot;menu&quot;:&quot;1&quot;,&quot;rating_style&quot;:&quot;NaN&quot;,&quot;rtl&quot;:false}}\" data-yrc-setup=\"\"><\/div>\r\n\t\t\t<script data-cfasync=\"false\" type=\"text\/javascript\">\r\n\t\t\t\tif( !window.YRC ) var YRC = {Data:{}};\r\n\t\t\t\tYRC.Data[\"69e0d9b9e5798\"] = {\"video\":\"Q7QF2GqrP48\"};\r\n\t\t\t\t(function(){\r\n\t\t\t\t\tif(!YRC.loaded){\r\n\t\t\t\t\t    YRC.loaded = true;\r\n\t\t\t\t\t\tfunction YRC_Loader(){\r\n\t\t\t\t\t\t\t\/\/YRC.loaded = true;\r\n\t\t\t\t\t\t\tYRC.is_pro = false;\r\n\t\t\t\t\t\t\tYRC.is_pb = false;\r\n\t\t\t\t\t\t\tYRC.lang = {\"form\":{\"Videos\":\"Videos\",\"Playlists\":\"Playlists\",\"Search\":\"Search\",\"Loading\":\"Loading\",\"more\":\"more\",\"Nothing_found\":\"Nothing found\",\"Prev\":\"Previous\",\"Next\":\"Next\",\"consent_statement\":\"Allow cookies?\",\"consent_button\":\"Allow\",\"consent_privacy_policy\":\"Privacy policy\"},\"fui\":{\"sort_by\":\"Sort by\",\"relevant\":\"Relevant\",\"latest\":\"Latest\",\"liked\":\"Liked\",\"title\":\"Title\",\"views\":\"Views\",\"duration\":\"Duration\",\"any\":\"Any\",\"_short\":\"Short\",\"medium\":\"Medium\",\"_long\":\"Long\",\"uploaded\":\"Uploaded\",\"all_time\":\"All time\",\"live_now\":\"Live Now\",\"today\":\"Today\",\"ago\":\"ago\",\"last\":\"Last\",\"day\":\"day\",\"days\":\"days\",\"week\":\"week\",\"weeks\":\"weeks\",\"month\":\"month\",\"months\":\"months\",\"year\":\"year\",\"years\":\"years\",\"older\":\"Older\",\"show_more\":\"Show More\",\"show_less\":\"Show Less\",\"reply\":\"REPLY\",\"view_replies\":\"View replies\",\"write_comment\":\"Write comment...\",\"billion\":\"B\",\"million\":\"M\",\"thousand\":\"K\",\"max_plain_number\":1000,\"wplocale\":\"en_US\"}};\r\n\t\t\t\t\t\t\tYRC.is_admin = false;\r\n\t\t\t\t\t\t\tvar script = document.createElement(\"script\");\r\n\t\t\t\t\t\t\t\tscript.setAttribute(\"data-cfasync\", \"false\");\r\n\t\t\t\t\t\t\t\tscript.setAttribute(\"type\", \"text\/javascript\");\r\n\t\t\t\t\t\t\t\tscript.src = \"https:\/\/www.programminginpython.com\/wp-content\/plugins\/yourchannel\/js\/yrc.js?1.2.9\";\r\n\t\t\t\t\t\t\t\tscript.id = \"yrc-script\";\r\n\t\t\t\t\t\t\t\tdocument.querySelector(\"head\").appendChild(script);\r\n\t\t\t\t\t\t\tvar style = document.createElement(\"link\");\r\n\t\t\t\t\t\t\t\tstyle.rel = \"stylesheet\";\r\n\t\t\t\t\t\t\t\tstyle.href = \"https:\/\/www.programminginpython.com\/wp-content\/plugins\/yourchannel\/css\/style.css?1.2.9\";\r\n\t\t\t\t\t\t\t\tstyle.type = \"text\/css\";\r\n\t\t\t\t\t\t\t\tdocument.querySelector(\"head\").appendChild(style);\r\n\t\t\t\t\t\t}\r\n\t\t\t\t\t\tif(window.jQuery){YRC_Loader();}else { var yrctimer2324 = window.setInterval(function(){\r\n\t\t\t\t\t\t\tif(window.jQuery){YRC_Loader(); window.clearInterval(yrctimer2324); }\r\n\t\t\t\t\t\t}, 250);}\r\n\t\t\t\t\t} else {if(YRC.EM)YRC.EM.trigger(\"yrc.newchannel\");}\r\n\t\t\t\t}());<\/script>\n<p style=\"text-align: center;\"><strong>You can also watch this video on YouTube <a href=\"https:\/\/www.youtube.com\/watch?v=Q7QF2GqrP48\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a><\/strong><\/p>\n<p>First, you need some module to connect to MySQL, So here I will install a module called `<strong>PyMySQL`<\/strong>, which is an interface to connect to a database from python. It implements the Python database API version 2.0. As I cover most of the tutorials in the site using Python 3, am using <code>PyMySQL<\/code>, for Python 2, there is another module called <code>MySQLdb<\/code>,\u00a0which is not supported in Python 3.<\/p>\n<p class=\"extra_btn_para\"><span class=\"wdg\"> <a href=\"https:\/\/github.com\/avinashn\/programminginpython.com\/tree\/master\/python_database_operations\" target=\"_blank\" rel=\"noopener noreferrer\"> <i class=\"fa fa-github fa-lg\"><\/i> Program on Github<\/a><\/span><\/p>\n<p>You can install pyMySQL\u00a0using either <code>pip<\/code> or <code>easy_install<\/code> for windows.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">pip install PyMySQL \r\n# OR \r\neasy_install PyMySQL<\/pre>\n<h4 class=\"post_h4\">Video Explanation<\/h4>\n<div class=\"yrc-shell-cover yrc-single\" data-yrc-uid=\"69e0d9b9e5ae6\" data-yrc-channel=\"{&quot;meta&quot;:{&quot;user&quot;:&quot;Programming In Python&quot;,&quot;channel&quot;:&quot;UCLBlXUMLYLxopRljNdLXOeQ&quot;,&quot;key&quot;:&quot;yrc_1683138754&quot;,&quot;apikey&quot;:&quot;AIzaSyC3vU6rFyzsO0shrRsAWmjuSiuWYTQhafc&quot;,&quot;cache&quot;:&quot;1440&quot;,&quot;channel_uploads&quot;:&quot;UULBlXUMLYLxopRljNdLXOeQ&quot;,&quot;onlyonce&quot;:&quot;&quot;,&quot;tag&quot;:&quot;&quot;,&quot;per_page&quot;:&quot;24&quot;,&quot;maxv&quot;:&quot;0&quot;,&quot;consent&quot;:{&quot;ask&quot;:&quot;&quot;,&quot;url&quot;:&quot;&quot;},&quot;ads&quot;:&quot;1&quot;,&quot;uid&quot;:&quot;69e0d9b9e5ae6&quot;,&quot;nocookie&quot;:&quot;&quot;,&quot;single&quot;:true},&quot;style&quot;:{&quot;colors&quot;:{&quot;item&quot;:{&quot;background&quot;:&quot;inherit&quot;},&quot;button&quot;:{&quot;background&quot;:&quot;#333&quot;,&quot;color&quot;:&quot;#fff&quot;},&quot;color&quot;:{&quot;text&quot;:&quot;#fff&quot;,&quot;link&quot;:&quot;inherit&quot;,&quot;menu&quot;:&quot;#000&quot;,&quot;meta&quot;:&quot;inherit&quot;}},&quot;theme&quot;:{&quot;videos&quot;:{&quot;style&quot;:&quot;__grid&quot;,&quot;thumb&quot;:[&quot;large&quot;,&quot;open&quot;],&quot;desc&quot;:&quot;&quot;,&quot;carousel&quot;:{&quot;thumbs&quot;:&quot;4&quot;,&quot;thumbs_to_slide&quot;:&quot;2&quot;,&quot;spacing&quot;:&quot;10&quot;},&quot;carousel_nav&quot;:{&quot;modifier&quot;:&quot;__sides&quot;,&quot;position&quot;:&quot;left-none&quot;,&quot;location&quot;:&quot;prepend&quot;,&quot;background&quot;:&quot;#fff&quot;,&quot;color&quot;:&quot;#000&quot;,&quot;font_size&quot;:&quot;2&quot;,&quot;border_radius&quot;:&quot;0&quot;}},&quot;a&quot;:&quot;1&quot;},&quot;fit&quot;:&quot;false&quot;,&quot;playlists&quot;:&quot;true&quot;,&quot;uploads&quot;:&quot;true&quot;,&quot;player_mode&quot;:&quot;1&quot;,&quot;truncate&quot;:&quot;&quot;,&quot;banner&quot;:&quot;true&quot;,&quot;thumb_margin&quot;:&quot;8&quot;,&quot;play_icon&quot;:&quot;hover&quot;,&quot;youtube_play_icon&quot;:&quot;&quot;,&quot;thumb_image_size&quot;:&quot;medium&quot;,&quot;default_tab&quot;:&quot;uploads&quot;,&quot;sticky&quot;:{&quot;enable&quot;:&quot;&quot;,&quot;width&quot;:&quot;400&quot;,&quot;position&quot;:&quot;bottom-right&quot;,&quot;only_above&quot;:&quot;768&quot;,&quot;margin&quot;:&quot;12&quot;},&quot;player&quot;:{&quot;show_desc&quot;:&quot;&quot;,&quot;show_meta&quot;:&quot;&quot;},&quot;menu&quot;:&quot;1&quot;,&quot;rating_style&quot;:&quot;NaN&quot;,&quot;rtl&quot;:false}}\" data-yrc-setup=\"\"><\/div>\r\n\t\t\t<script data-cfasync=\"false\" type=\"text\/javascript\">\r\n\t\t\t\tif( !window.YRC ) var YRC = {Data:{}};\r\n\t\t\t\tYRC.Data[\"69e0d9b9e5ae6\"] = {\"video\":\"Q7QF2GqrP48\"};\r\n\t\t\t\t(function(){\r\n\t\t\t\t\tif(!YRC.loaded){\r\n\t\t\t\t\t    YRC.loaded = true;\r\n\t\t\t\t\t\tfunction YRC_Loader(){\r\n\t\t\t\t\t\t\t\/\/YRC.loaded = true;\r\n\t\t\t\t\t\t\tYRC.is_pro = false;\r\n\t\t\t\t\t\t\tYRC.is_pb = false;\r\n\t\t\t\t\t\t\tYRC.lang = {\"form\":{\"Videos\":\"Videos\",\"Playlists\":\"Playlists\",\"Search\":\"Search\",\"Loading\":\"Loading\",\"more\":\"more\",\"Nothing_found\":\"Nothing found\",\"Prev\":\"Previous\",\"Next\":\"Next\",\"consent_statement\":\"Allow cookies?\",\"consent_button\":\"Allow\",\"consent_privacy_policy\":\"Privacy policy\"},\"fui\":{\"sort_by\":\"Sort by\",\"relevant\":\"Relevant\",\"latest\":\"Latest\",\"liked\":\"Liked\",\"title\":\"Title\",\"views\":\"Views\",\"duration\":\"Duration\",\"any\":\"Any\",\"_short\":\"Short\",\"medium\":\"Medium\",\"_long\":\"Long\",\"uploaded\":\"Uploaded\",\"all_time\":\"All time\",\"live_now\":\"Live Now\",\"today\":\"Today\",\"ago\":\"ago\",\"last\":\"Last\",\"day\":\"day\",\"days\":\"days\",\"week\":\"week\",\"weeks\":\"weeks\",\"month\":\"month\",\"months\":\"months\",\"year\":\"year\",\"years\":\"years\",\"older\":\"Older\",\"show_more\":\"Show More\",\"show_less\":\"Show Less\",\"reply\":\"REPLY\",\"view_replies\":\"View replies\",\"write_comment\":\"Write comment...\",\"billion\":\"B\",\"million\":\"M\",\"thousand\":\"K\",\"max_plain_number\":1000,\"wplocale\":\"en_US\"}};\r\n\t\t\t\t\t\t\tYRC.is_admin = false;\r\n\t\t\t\t\t\t\tvar script = document.createElement(\"script\");\r\n\t\t\t\t\t\t\t\tscript.setAttribute(\"data-cfasync\", \"false\");\r\n\t\t\t\t\t\t\t\tscript.setAttribute(\"type\", \"text\/javascript\");\r\n\t\t\t\t\t\t\t\tscript.src = \"https:\/\/www.programminginpython.com\/wp-content\/plugins\/yourchannel\/js\/yrc.js?1.2.9\";\r\n\t\t\t\t\t\t\t\tscript.id = \"yrc-script\";\r\n\t\t\t\t\t\t\t\tdocument.querySelector(\"head\").appendChild(script);\r\n\t\t\t\t\t\t\tvar style = document.createElement(\"link\");\r\n\t\t\t\t\t\t\t\tstyle.rel = \"stylesheet\";\r\n\t\t\t\t\t\t\t\tstyle.href = \"https:\/\/www.programminginpython.com\/wp-content\/plugins\/yourchannel\/css\/style.css?1.2.9\";\r\n\t\t\t\t\t\t\t\tstyle.type = \"text\/css\";\r\n\t\t\t\t\t\t\t\tdocument.querySelector(\"head\").appendChild(style);\r\n\t\t\t\t\t\t}\r\n\t\t\t\t\t\tif(window.jQuery){YRC_Loader();}else { var yrctimer2324 = window.setInterval(function(){\r\n\t\t\t\t\t\t\tif(window.jQuery){YRC_Loader(); window.clearInterval(yrctimer2324); }\r\n\t\t\t\t\t\t}, 250);}\r\n\t\t\t\t\t} else {if(YRC.EM)YRC.EM.trigger(\"yrc.newchannel\");}\r\n\t\t\t\t}());<\/script>\n<p style=\"text-align: center;\"><strong>Check this video on YouTube <a href=\"https:\/\/youtu.be\/Q7QF2GqrP48\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>.<\/strong><\/p>\n<blockquote><p>Ad:<br \/>\n<span style=\"font-size: 18pt;\">Learn Python Programming Masterclass \u2013 <a href=\"https:\/\/bit.ly\/python-programming-masterclass\" target=\"_blank\" rel=\"noopener\">Enroll Now<\/a>.<\/span><br \/>\n<span style=\"font-size: 12px;\">Udemy<\/span><\/p><\/blockquote>\n<p>So before performing these database operations, I will first create a database named \u2018python_tutorials\u2019 in phpMyAdmin locally.<\/p>\n<figure id=\"attachment_644\" class=\"wp-caption aligncenter\" style=\"width: 709px;\" aria-describedby=\"caption-attachment-644\">\n<p><figure id=\"attachment_438\" aria-describedby=\"caption-attachment-438\" style=\"width: 709px\" class=\"wp-caption aligncenter\"><img decoding=\"async\" class=\"size-full wp-image-438 lazyload\" data-src=\"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/03\/python_db1.png\" alt=\"How to access MySQL database in Python\" width=\"709\" height=\"326\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 709px; --smush-placeholder-aspect-ratio: 709\/326;\" \/><figcaption id=\"caption-attachment-438\" class=\"wp-caption-text\">How to access MySQL database in Python<\/figcaption><\/figure><\/figure>\n<h4 class=\"post_h4\">Create a MySQL database table in Python<\/h4>\n<p>First I will import the <code>pyMySQL<\/code> package, which I installed before.<\/p>\n<p><code class=\"EnlighterJSRAW\" data-enlighter-language=\"python\">import pymysql<\/code><\/p>\n<p>Now I need to connect to the database, this <code>pymysql<\/code> has a function called <code>connect('host', 'username', 'password', 'database_name')<\/code> which takes 4 arguments.<\/p>\n<p><code class=\"EnlighterJSRAW\" data-enlighter-language=\"python\">db = pymysql.connect(\"localhost\", \"root\", \"\", \"python_tutorials\")<\/code><\/p>\n<p>Next, I will prepare a <code>cursor<\/code> to execute the SQL\u00a0queries.<\/p>\n<p><code class=\"EnlighterJSRAW\" data-enlighter-language=\"python\">cursor = db.cursor()<\/code><\/p>\n<p>So, now I can write queries like <code>cursor.execute(\"SQL QUERY\")<\/code><\/p>\n<p>I will create a new database table by executing the following query,<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\">sql = \"\"\"CREATE TABLE PERSON (\r\n   ID INT NOT NULL,\r\n   FIRST_NAME  CHAR(20) NOT NULL,\r\n   LAST_NAME  CHAR(20),\r\n   AGE INT,\r\n   SEX CHAR(1),\r\n   PRIMARY KEY (ID) )\"\"\"\r\n\r\ncursor.execute(sql)<\/pre>\n<p>The above code creates a new table.<\/p>\n<figure id=\"attachment_647\" class=\"wp-caption aligncenter\" style=\"width: 1081px;\" aria-describedby=\"caption-attachment-647\">\n<p><figure id=\"attachment_439\" aria-describedby=\"caption-attachment-439\" style=\"width: 1081px\" class=\"wp-caption alignnone\"><img decoding=\"async\" class=\"size-full wp-image-439 lazyload\" data-src=\"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/03\/python_db_11.png\" alt=\"How to access MySQL database in Python\" width=\"1081\" height=\"307\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 1081px; --smush-placeholder-aspect-ratio: 1081\/307;\" \/><figcaption id=\"caption-attachment-439\" class=\"wp-caption-text\">How to access MySQL database in Python<\/figcaption><\/figure><\/figure>\n<p>Finally, I will close the connection.<\/p>\n<p><code class=\"EnlighterJSRAW\" data-enlighter-language=\"python\">db.close()<\/code><\/p>\n<p class=\"extra_btn_para\"><span class=\"wdg\"> <a href=\"https:\/\/github.com\/avinashn\/programminginpython.com\/tree\/master\/python_database_operations\" target=\"_blank\" rel=\"noopener noreferrer\"> <i class=\"fa fa-github fa-lg\"><\/i> Program on Github<\/a><\/span><\/p>\n<p><span style=\"text-decoration: underline;\"><strong>Code:<\/strong><\/span><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\">__author__ = 'Avinash'\r\n\r\nimport pymysql\r\n\r\n# Open database connection\r\ndb = pymysql.connect(\"localhost\", \"root\", \"\", \"python_tutorials\")\r\n\r\n# prepare a cursor object using cursor() method\r\ncursor = db.cursor()\r\n\r\n# Drop table if it already exist using execute() method.\r\ncursor.execute(\"DROP TABLE IF EXISTS PERSON\")\r\n\r\n# Create table as per requirement\r\nsql = \"\"\"CREATE TABLE PERSON (\r\n   ID INT NOT NULL,\r\n   FIRST_NAME  CHAR(20) NOT NULL,\r\n   LAST_NAME  CHAR(20),\r\n   AGE INT,\r\n   SEX CHAR(1),\r\n   PRIMARY KEY (ID) )\"\"\"\r\n\r\ncursor.execute(sql)\r\n\r\n# disconnect from server\r\ndb.close()<\/pre>\n<h4 class=\"post_h4\">Inserting data into a table<\/h4>\n<p>Similar to creating a table, here also I will use a SQL query to insert data, but here \u00a0I also use <code>try<\/code> and <code>except<\/code>, so if any error or problem occurs while inserting the data, the whole operation can be rolled back.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\">sql = \"\"\"INSERT INTO PERSON(ID, FIRST_NAME,\r\n   LAST_NAME, AGE, SEX )\r\n   VALUES (1, 'ABC', 'ABCD', 20, 'M')\"\"\"\r\ntry:\r\n    # Execute the SQL command\r\n    cursor.execute(sql)\r\n    # Commit your changes in the database\r\n    db.commit()\r\nexcept pymysql.Error:\r\n    # Rollback in case there is any error\r\n    db.rollback()<\/pre>\n<figure id=\"attachment_650\" class=\"wp-caption aligncenter\" style=\"width: 633px;\" aria-describedby=\"caption-attachment-650\">\n<p><figure id=\"attachment_440\" aria-describedby=\"caption-attachment-440\" style=\"width: 633px\" class=\"wp-caption alignnone\"><img decoding=\"async\" class=\"size-full wp-image-440 lazyload\" data-src=\"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/03\/python_db_insert1.png\" alt=\"How to access MySQL database in Python\" width=\"633\" height=\"348\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 633px; --smush-placeholder-aspect-ratio: 633\/348;\" \/><figcaption id=\"caption-attachment-440\" class=\"wp-caption-text\">How to access MySQL database in Python<\/figcaption><\/figure><\/figure>\n<h4 class=\"post_h4\">Update data in the table<\/h4>\n<p>This update operation is also similar to insert operation, I just will change my SQL query here. So I will change the age column value to 30 if the person is male(M)<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\">sql = \"UPDATE PERSON SET AGE = 30  WHERE SEX = '%c'\" % ('M')\r\ntry:\r\n    # Execute the SQL command\r\n    cursor.execute(sql)\r\n    # Commit your changes in the database\r\n    db.commit()\r\nexcept:\r\n   # Rollback in case there is any error\r\n   db.rollback()<\/pre>\n<figure id=\"attachment_651\" class=\"wp-caption aligncenter\" style=\"width: 566px;\" aria-describedby=\"caption-attachment-651\">\n<p><figure id=\"attachment_441\" aria-describedby=\"caption-attachment-441\" style=\"width: 566px\" class=\"wp-caption alignnone\"><img decoding=\"async\" class=\"size-full wp-image-441 lazyload\" data-src=\"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/03\/python_db_update1.png\" alt=\"How to access MySQL database in Python\" width=\"566\" height=\"260\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 566px; --smush-placeholder-aspect-ratio: 566\/260;\" \/><figcaption id=\"caption-attachment-441\" class=\"wp-caption-text\">How to access MySQL database in Python<\/figcaption><\/figure><\/figure>\n<p class=\"extra_btn_para\"><span class=\"wdg\"> <a href=\"https:\/\/github.com\/avinashn\/programminginpython.com\/tree\/master\/python_database_operations\" target=\"_blank\" rel=\"noopener noreferrer\"> <i class=\"fa fa-github fa-lg\"><\/i> Program on Github<\/a><\/span><\/p>\n<h4 class=\"post_h4\">Read data from the table<\/h4>\n<p>For reading the data, I will use a simple query to get all data, but for showing the data, I will loop through all the data and fetch single rows data and print them.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\">sql = \"SELECT * FROM PERSON\"\r\ntry:\r\n    # Execute the SQL command\r\n    cursor.execute(sql)\r\n    # Fetch all the rows in a list of lists.\r\n    results = cursor.fetchall()\r\n    for row in results:\r\n        fname = row[1]\r\n        lname = row[2]\r\n        age = row[3]\r\n        sex = row[4]\r\n        # Now print fetched result\r\n        print(\"fname = %s,lname = %s,age = %d,sex = %s\" % \\\r\n        (fname, lname, age, sex, ))\r\nexcept:\r\n   print(\"Error: unable to fetch data\")<\/pre>\n<pre class=\"lang:default decode:true \"><\/pre>\n<figure id=\"attachment_653\" class=\"wp-caption aligncenter\" style=\"width: 961px;\" aria-describedby=\"caption-attachment-653\"><img decoding=\"async\" class=\"size-full wp-image-653 lazyload\" data-src=\"http:\/\/programminginpython.com\/wp-content\/uploads\/2017\/08\/python_db_read.png\" data-sizes=\"(max-width: 961px) 100vw, 961px\" data-srcset=\"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2017\/08\/python_db_read.png 961w, https:\/\/www.programminginpython.com\/wp-content\/uploads\/2017\/08\/python_db_read-300x31.png 300w, https:\/\/www.programminginpython.com\/wp-content\/uploads\/2017\/08\/python_db_read-768x78.png 768w\" alt=\"How to access MySQL database in python - programminginpython.com\" width=\"961\" height=\"98\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 961px; --smush-placeholder-aspect-ratio: 961\/98;\" \/><figcaption id=\"caption-attachment-653\" class=\"wp-caption-text\">How to access MySQL database in python \u2013 programminginpython.com<\/figcaption><\/figure>\n<h4 class=\"post_h4\">Delete data from the table<\/h4>\n<p>For deleting I will use a simple query with some condition to delete elements\/rows from the table.<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"python\">sql = \"DELETE FROM PERSON WHERE AGE &gt; '%d'\" % (29)\r\ntry:\r\n    # Execute the SQL command\r\n    cursor.execute(sql)\r\n    # Commit your changes in the database\r\n    db.commit()\r\nexcept:\r\n   # Rollback in case there is any error\r\n   db.rollback()<\/pre>\n<pre class=\"lang:default decode:true\"><\/pre>\n<p>So after running the above code, the only row in the table gets\u00a0deleted as it satisfies the condition I gave i.e age &gt; 29.<\/p>\n<figure id=\"attachment_654\" class=\"wp-caption aligncenter\" style=\"width: 578px;\" aria-describedby=\"caption-attachment-654\">\n<p><figure id=\"attachment_442\" aria-describedby=\"caption-attachment-442\" style=\"width: 578px\" class=\"wp-caption aligncenter\"><img decoding=\"async\" class=\"size-full wp-image-442 lazyload\" data-src=\"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/03\/python_db_delete1.png\" alt=\"How to access MySQL database in Python\" width=\"578\" height=\"238\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 578px; --smush-placeholder-aspect-ratio: 578\/238;\" \/><figcaption id=\"caption-attachment-442\" class=\"wp-caption-text\">How to access MySQL database in Python<\/figcaption><\/figure><\/figure>\n<p>That\u2019s it for the post and we performed MySQL database CRUD in Python. I hope now you learned how to perform CRUD operations on a DB and its tables.<\/p>\n<blockquote><p>Ad:<br \/>\n<span style=\"font-size: 18pt;\">Learn Python Programming Masterclass \u2013 <a href=\"https:\/\/bit.ly\/python-programming-masterclass\" target=\"_blank\" rel=\"noopener\">Enroll Now<\/a>.<\/span><br \/>\n<span style=\"font-size: 12px;\">Udemy<\/span><\/p><\/blockquote>\n<p class=\"extra_btn_para\"><span class=\"wdg\"> <a href=\"https:\/\/github.com\/avinashn\/programminginpython.com\/tree\/master\/python_database_operations\" target=\"_blank\" rel=\"noopener noreferrer\"> <i class=\"fa fa-github fa-lg\"><\/i> Program on GitHub<\/a><\/span><\/p>\n<p>Feel free to look at my other Python posts on <a href=\"http:\/\/programminginpython.com\/category\/gui-programs\/\" target=\"_blank\" rel=\"noopener noreferrer\">GUI Programs<\/a>, <a href=\"\/category\/math-programs\/\" target=\"_blank\" rel=\"noopener noreferrer\">Math Programs<\/a> and <a href=\"\/category\/basic-programs\/\" target=\"_blank\" rel=\"noopener noreferrer\">Basic Programs<\/a> in Python.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hello everyone, welcome back to programminginpython.com. Here I am going to show you how to access MySQL database and perform all database operations on the database in Python. In most of the applications, you need &hellip;<\/p>\n","protected":false},"author":1,"featured_media":437,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[146,145,148,144,147],"class_list":["post-53","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","tag-crud","tag-database","tag-database-crud","tag-mysql","tag-pymysql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to perform MySQL database CRUD in Python - Programming In Python<\/title>\n<meta name=\"description\" content=\"Perform MySQL CRUD in python using PyMySQL to connect to MySQL database and write pythonic database queries to perform CRUD operations on DB.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.programminginpython.com\/access-mysql-database-python\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to perform MySQL database CRUD in Python - Programming In Python\" \/>\n<meta property=\"og:description\" content=\"Perform MySQL CRUD in python using PyMySQL to connect to MySQL database and write pythonic database queries to perform CRUD operations on DB.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.programminginpython.com\/access-mysql-database-python\/\" \/>\n<meta property=\"og:site_name\" content=\"Programming In Python\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/programminginpython\" \/>\n<meta property=\"article:published_time\" content=\"2023-03-30T04:56:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-04-27T14:21:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/03\/Python_database_operations-1920x4001-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"400\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"AVINASH NETHALA\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@python_pip\" \/>\n<meta name=\"twitter:site\" content=\"@python_pip\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"AVINASH NETHALA\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to perform MySQL database CRUD in Python - Programming In Python","description":"Perform MySQL CRUD in python using PyMySQL to connect to MySQL database and write pythonic database queries to perform CRUD operations on DB.","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:\/\/www.programminginpython.com\/access-mysql-database-python\/","og_locale":"en_US","og_type":"article","og_title":"How to perform MySQL database CRUD in Python - Programming In Python","og_description":"Perform MySQL CRUD in python using PyMySQL to connect to MySQL database and write pythonic database queries to perform CRUD operations on DB.","og_url":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/","og_site_name":"Programming In Python","article_publisher":"https:\/\/www.facebook.com\/programminginpython","article_published_time":"2023-03-30T04:56:55+00:00","article_modified_time":"2023-04-27T14:21:55+00:00","og_image":[{"width":1920,"height":400,"url":"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/03\/Python_database_operations-1920x4001-1.png","type":"image\/png"}],"author":"AVINASH NETHALA","twitter_card":"summary_large_image","twitter_creator":"@python_pip","twitter_site":"@python_pip","twitter_misc":{"Written by":"AVINASH NETHALA","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/#article","isPartOf":{"@id":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/"},"author":{"name":"AVINASH NETHALA","@id":"https:\/\/www.programminginpython.com\/#\/schema\/person\/9a3c14fe46d422ebf783ee61de1e788c"},"headline":"How to perform MySQL database CRUD in Python","datePublished":"2023-03-30T04:56:55+00:00","dateModified":"2023-04-27T14:21:55+00:00","mainEntityOfPage":{"@id":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/"},"wordCount":609,"commentCount":0,"publisher":{"@id":"https:\/\/www.programminginpython.com\/#organization"},"image":{"@id":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/#primaryimage"},"thumbnailUrl":"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/03\/Python_database_operations-1920x4001-1.png","keywords":["CRUD","database","database CRUD","MySQL","pyMySQL"],"articleSection":["Databases"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.programminginpython.com\/access-mysql-database-python\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/","url":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/","name":"How to perform MySQL database CRUD in Python - Programming In Python","isPartOf":{"@id":"https:\/\/www.programminginpython.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/#primaryimage"},"image":{"@id":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/#primaryimage"},"thumbnailUrl":"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/03\/Python_database_operations-1920x4001-1.png","datePublished":"2023-03-30T04:56:55+00:00","dateModified":"2023-04-27T14:21:55+00:00","description":"Perform MySQL CRUD in python using PyMySQL to connect to MySQL database and write pythonic database queries to perform CRUD operations on DB.","breadcrumb":{"@id":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.programminginpython.com\/access-mysql-database-python\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/#primaryimage","url":"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/03\/Python_database_operations-1920x4001-1.png","contentUrl":"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/03\/Python_database_operations-1920x4001-1.png","width":1920,"height":400,"caption":"How to access MySQL database in Python"},{"@type":"BreadcrumbList","@id":"https:\/\/www.programminginpython.com\/access-mysql-database-python\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.programminginpython.com\/"},{"@type":"ListItem","position":2,"name":"How to perform MySQL database CRUD in Python"}]},{"@type":"WebSite","@id":"https:\/\/www.programminginpython.com\/#website","url":"https:\/\/www.programminginpython.com\/","name":"Programming In Python","description":"All About Python","publisher":{"@id":"https:\/\/www.programminginpython.com\/#organization"},"alternateName":"pip","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.programminginpython.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.programminginpython.com\/#organization","name":"Programming In Python","alternateName":"PIP","url":"https:\/\/www.programminginpython.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.programminginpython.com\/#\/schema\/logo\/image\/","url":"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/04\/pip_logo_500_500.png","contentUrl":"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/04\/pip_logo_500_500.png","width":500,"height":500,"caption":"Programming In Python"},"image":{"@id":"https:\/\/www.programminginpython.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/programminginpython","https:\/\/x.com\/python_pip","https:\/\/www.youtube.com\/programminginpython","https:\/\/github.com\/avinashn\/programminginpython.com"]},{"@type":"Person","@id":"https:\/\/www.programminginpython.com\/#\/schema\/person\/9a3c14fe46d422ebf783ee61de1e788c","name":"AVINASH NETHALA","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.programminginpython.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ed52e7670d7db94820c7430d324103ccdecb16d86611d5b29064aa9ce25a958b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ed52e7670d7db94820c7430d324103ccdecb16d86611d5b29064aa9ce25a958b?s=96&d=mm&r=g","caption":"AVINASH NETHALA"},"sameAs":["https:\/\/www.programminginpython.com\/"],"url":"https:\/\/www.programminginpython.com\/author\/avinash\/"}]}},"jetpack_featured_media_url":"https:\/\/www.programminginpython.com\/wp-content\/uploads\/2023\/03\/Python_database_operations-1920x4001-1.png","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.programminginpython.com\/wp-json\/wp\/v2\/posts\/53","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.programminginpython.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.programminginpython.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.programminginpython.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.programminginpython.com\/wp-json\/wp\/v2\/comments?post=53"}],"version-history":[{"count":4,"href":"https:\/\/www.programminginpython.com\/wp-json\/wp\/v2\/posts\/53\/revisions"}],"predecessor-version":[{"id":698,"href":"https:\/\/www.programminginpython.com\/wp-json\/wp\/v2\/posts\/53\/revisions\/698"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.programminginpython.com\/wp-json\/wp\/v2\/media\/437"}],"wp:attachment":[{"href":"https:\/\/www.programminginpython.com\/wp-json\/wp\/v2\/media?parent=53"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.programminginpython.com\/wp-json\/wp\/v2\/categories?post=53"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.programminginpython.com\/wp-json\/wp\/v2\/tags?post=53"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}