{"id":706,"date":"2022-02-21T14:32:25","date_gmt":"2022-02-21T19:32:25","guid":{"rendered":"https:\/\/molecularsciences.org\/content\/?p=706"},"modified":"2022-02-21T14:32:27","modified_gmt":"2022-02-21T19:32:27","slug":"python3-with-mysql-database","status":"publish","type":"post","link":"https:\/\/molecularsciences.org\/content\/python3-with-mysql-database\/","title":{"rendered":"Python3 with MySQL database"},"content":{"rendered":"\n<p>If you don&#8217;t already have MySQL installed, install it with the following commands (mac only):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ brew info mysql\n$ brew install mysql\n$ brew tap homebrew\/services<\/code><\/pre>\n\n\n\n<p>Run the following commands to start or stop mysql. Start MySQL before proceeding:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ brew services stop mysql\n$ brew services start mysql<\/code><\/pre>\n\n\n\n<p>To see available sevices:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ brew services list<\/code><\/pre>\n\n\n\n<p>To check MySQL version:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysql -V<\/code><\/pre>\n\n\n\n<p>Set root password<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysqladmin -u root password 'pass'<\/code><\/pre>\n\n\n\n<p>replace pass with you new password. To login:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysql -u root -p<\/code><\/pre>\n\n\n\n<p>Then run the following commands:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>mysql> create database fatwire;\nmysql> use fatwire;\nmysql> create table fatwire (id int(15), name varchar(255));<\/code><\/pre>\n\n\n\n<p>Leave this terminal open, and open a new terminal to the following steps.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"connection-python3-to-mysql\">Connection python3 to MySQL<\/h3>\n\n\n\n<p>Install PyMySQL as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ pip3 install pymsql<\/code><\/pre>\n\n\n\n<p>Then test connection with the following code (testconnection.py):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import pymysql.cursors\ncon = pymysql.connect(host = 'localhost', user = 'root', password = 'pass', db = 'fatwire', cursorclass = pymysql.cursors.DictCursor)\nprint('Connection Successfull');<\/code><\/pre>\n\n\n\n<p>To run:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ python3 testconnection.py<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"insert-data-into-mysql-database-using-python3\">Insert data into MySQL database using Python3<\/h3>\n\n\n\n<p>See the following code:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import pymysql.cursors\ncon = pymysql.connect(host = 'localhost', user = 'root', password = 'pass', db = 'fatwire', cursorclass = pymysql.cursors.DictCursor)\n\ntry:\n    cur = con.cursor()\n\n    a = 12331\n    b = 'The Nugget'\n\n    sql = \"Insert into fatwire (id, name) \" + \" values (%s, %s)\"\n    cur.execute(sql,(a, b))\n    con.commit()\n\nfinally:\n    con.close()<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>If you don&#8217;t already have MySQL installed, install it with the following commands (mac only): Run the following commands to start or stop mysql. Start MySQL before proceeding: To see available sevices: To check MySQL version: Set root password replace pass with you new password. To login: Then run the following commands: Leave this terminal [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[204,203],"tags":[27,137],"class_list":["post-706","post","type-post","status-publish","format-standard","hentry","category-mysql","category-python","tag-mysql","tag-python"],"_links":{"self":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/706","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/comments?post=706"}],"version-history":[{"count":1,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/706\/revisions"}],"predecessor-version":[{"id":707,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/706\/revisions\/707"}],"wp:attachment":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media?parent=706"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/categories?post=706"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/tags?post=706"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}