{"id":3177,"date":"2018-06-26T12:45:57","date_gmt":"2018-06-26T12:45:57","guid":{"rendered":"http:\/\/truelogic.org\/wordpress\/?p=3177"},"modified":"2018-07-04T03:26:47","modified_gmt":"2018-07-04T03:26:47","slug":"python-2-7-working-with-mysql-databases","status":"publish","type":"post","link":"https:\/\/truelogic.org\/wordpress\/2018\/06\/26\/python-2-7-working-with-mysql-databases\/","title":{"rendered":"Python 2.7 &#8211; Working with MySQL databases"},"content":{"rendered":"            <script type=\"text\/javascript\" src=\"https:\/\/truelogic.org\/wordpress\/wp-content\/plugins\/wordpress-code-snippet\/scripts\/shBrushPython.js\"><\/script>\n<h3>Overview<\/h3>\n<p>Most modern applications need to interact with a database some time or the other. The most common databases in use are MySQL if its a Linux platform and MS SQL Server or MS Access if its a Windows platform. In this article we look how to work with MySQL databases. This article assumes that you are already familiar with MySQL and know basic database commands and concepts.<\/p>\n<p>There are quite a few Python libraries available for working with MySQL . For our purposes we will use PyMySQL because its easy to work with and its small. It can be installed with pip:<\/p>\n<pre>pip3 install PyMySQL\r\n<\/pre>\n<h3><\/h3>\n<h3>PyMySql Usage<\/h3>\n<p>This package has only a few classes:<\/p>\n<ul>\n<li>pymysql.connections.Connection &#8211; the main database connection class<\/li>\n<li>pymysql.cursors.Cursor &#8211; the default buffered cursor which returns data as Python lists<\/li>\n<li>pymysql.cursors.SSCursor &#8211; unbuffered cursor which returns data as Python lists but meant for handling large amounts of data or connecting to remote servers over a slow network<\/li>\n<li>pymysql.cursors.DictCursor &#8211; returns data as Python dictionaries instead of lists.<\/li>\n<li>pymysql.cursors.SSDictCursor &#8211; same as DictCursor but with the functionality of SSCursor<\/li>\n<\/ul>\n<p>You can find the detailed documentation here: <a href=\"https:\/\/pymysql.readthedocs.io\/en\/latest\/modules\/index.html\" target=\"_blank\" rel=\"noopener\">https:\/\/pymysql.readthedocs.io\/en\/latest\/modules\/index.html<\/a><\/p>\n<h3>Sample Code<\/h3>\n<p>Given below is sample code which shows how to interact with MySQL. Basically it is your responsibility to create the SQL queries and sanitize the data before passing it to the server since PyMySQL does not do things like string escaping or parametrization. The <a href=\"https:\/\/www.python.org\/dev\/peps\/pep-0249\/#frequently-asked-questions\" target=\"_blank\" rel=\"noopener\">Python Database API specifications<\/a> provides a lot of capabilities but it is up to the libraries that implement this specification, to decide how much of the specification they want to put in place.<\/p>\n<p>What we have below is code which takes in a database connection and dumps out the details of all the tables within that database. It executes and parses the following SQL commands:<\/p>\n<ul>\n<li>SHOW TABLES<\/li>\n<li>DESCRIBE &lt;table&gt;<\/li>\n<li>SHOW INDEX FROM &lt;table&gt;<\/li>\n<\/ul>\n<p>We use the DictCursor mode for all the SQL queries. This is specified when opening the database connection.<\/p>\n<p><pre class=\"brush: python\">import pymysql.cursors\r\n\r\n\r\n&quot;&quot;&quot;\r\n Get the list of all the tables \r\n&quot;&quot;&quot;\r\ndef getTables(connection):\r\n    tables = []\r\n    with connection.cursor() as cursor:\r\n        sql = &quot;show tables&quot;\r\n\tcursor.execute(sql)\r\n        result = cursor.fetchall()\r\n\tfor row in result:\r\n\t\tfor key, value in row.items():\r\n\t\t\ttables.append(value)\r\n    return tables\r\n\r\n&quot;&quot;&quot;\r\n Get all the info for a single table\r\n&quot;&quot;&quot;\r\ndef getTableInfo(connection, tableName):\r\n   with connection.cursor() as cursor:\r\n        sql = &quot;describe `&quot; + tableName + &quot;`&quot;\r\n\tcursor.execute(sql)\r\n        result = cursor.fetchall()\r\n\tfor row in result:\r\n\t\tprint row[&quot;Field&quot;], &quot; &quot;, row[&quot;Type&quot;], &quot; &quot;, row[&quot;Key&quot;]\r\n\r\n&quot;&quot;&quot;\r\n  Get the list of all the indexes in a table\r\n&quot;&quot;&quot;\r\ndef getTableIndexes(connection, tableName):\r\n   with connection.cursor() as cursor:\r\n        sql = &quot;show index from  `&quot; + tableName + &quot;`&quot;\r\n\tcursor.execute(sql)\r\n        result = cursor.fetchall()\r\n\tprint &quot;Key name, Column&quot;\r\n\tfor row in result:\r\n\t\tprint row[&quot;Key_name&quot;], &quot;,&quot;, row[&quot;Column_name&quot;]\r\n\r\n\r\n\r\n\r\n\r\n# Connect to the database\r\nconnection = pymysql.connect(host=&#039;localhost&#039;,\r\n                             user=&#039;******&#039;,\r\n                             password=&#039;****&#039;,\r\n                             db=&#039;sampledb&#039;,\r\n                             charset=&#039;utf8mb4&#039;,\r\n                             cursorclass=pymysql.cursors.DictCursor)\r\n\r\ntry:\r\n\ttables = getTables(connection)\r\n\t\r\n\tfor table in tables:\r\n\t\tprint &quot;=====================================&quot;\r\n\t\tprint table\r\n\t\tprint &quot;.....................................&quot;\r\n\t\tgetTableInfo(connection, table)\r\n\t\tprint &quot;.....................................&quot;\r\n\t\tprint &quot;Indexes&quot;\r\n\t\tgetTableIndexes(connection, table)\r\n\t\tprint &quot;\\n\\n&quot;\r\n\r\n\r\n \r\nfinally:\r\n    connection.close()\r\n<\/pre><\/p>\n<p>A part of the output is given below:<\/p>\n<pre>=====================================\r\nBilling\r\n.....................................\r\nid bigint(20) PRI\r\nperson_id bigint(20) MUL\r\npayment_type tinyint(4)\r\naccount_id varchar(100)\r\ncard_number varchar(20)\r\nexp_month varchar(2)\r\nexp_year varchar(4)\r\npfp_string varchar(200)\r\nauthorization_id varchar(200)\r\ncvv_code varchar(10)\r\ncard_name varchar(100)\r\naddress_1 varchar(100)\r\naddress_2 varchar(100)\r\ncity varchar(30)\r\nstate varchar(30)\r\ncountry varchar(30)\r\nzip varchar(30)\r\nstatus tinyint(4)\r\nlast_billing_type varchar(20)\r\nlast_billing_action varchar(20)\r\nlast_billing_error varchar(255)\r\nlast_billing_timestamp datetime\r\ndate_created timestamp\r\nlast_updated timestamp\r\n.....................................\r\nIndexes\r\nKey name, Column\r\nPRIMARY , id\r\nBILLING_PERSON_ID , person_id\r\n\r\n=====================================\r\nBilling_Information\r\n.....................................\r\nid bigint(20) PRI\r\nbusiness_id bigint(20)\r\npayment_type tinyint(4)\r\ncard_number varchar(20)\r\nexpiry_month varchar(2)\r\nexpiry_year varchar(4)\r\npfp_string varchar(50)\r\nauthorization_id varchar(50)\r\ncvv_code varchar(10)\r\ncard_name varchar(100)\r\naddress_1 varchar(100)\r\naddress_2 varchar(100)\r\ncity varchar(30)\r\nstate varchar(30)\r\nzip varchar(30)\r\nactive tinyint(4)\r\nlast_billing_type varchar(20)\r\nlast_paypal_action varchar(20)\r\nlast_paypal_error varchar(255)\r\nlast_paypal_timestamp datetime\r\n.....................................\r\nIndexes\r\nKey name, Column\r\nPRIMARY , id\r\n\r\n=====================================\r\nBookmarks\r\n.....................................\r\nid bigint(20) PRI\r\nperson_id bigint(20) MUL\r\ngraph_id bigint(20) MUL\r\ndate_added datetime\r\n.....................................\r\nIndexes\r\nKey name, Column\r\nPRIMARY , id\r\nperson_id , person_id\r\ngraph_id , graph_id\r\nperson_id_graph_id , person_id\r\nperson_id_graph_id , graph_id\r\n\r\n<\/pre>\n<p>In the next articlewe see <a href=\"https:\/\/truelogic.org\/wordpress\/2018\/07\/04\/python-2-7-working-with-ms-sql-server-databases\/\" target=\"_blank\" rel=\"noopener\">how to work with MS SQL Server databases.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>Overview Most modern applications need to interact with a database some time or the other. The most common databases in use are MySQL if its <a class=\"mh-excerpt-more\" href=\"https:\/\/truelogic.org\/wordpress\/2018\/06\/26\/python-2-7-working-with-mysql-databases\/\" title=\"Python 2.7 &#8211; Working with MySQL databases\">[&#8230;]<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":2107,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10,297],"tags":[],"class_list":["post-3177","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programming","category-python"],"_links":{"self":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts\/3177","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/comments?post=3177"}],"version-history":[{"count":4,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts\/3177\/revisions"}],"predecessor-version":[{"id":3193,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/posts\/3177\/revisions\/3193"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/media\/2107"}],"wp:attachment":[{"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/media?parent=3177"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/categories?post=3177"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/truelogic.org\/wordpress\/wp-json\/wp\/v2\/tags?post=3177"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}