Python3 with MySQL database
If you don't already have MySQL installed, install it with the following commands (mac only):
$ brew info mysql
$ brew install mysql
$ brew tap homebrew/services
Run the following commands to start or stop mysql. Start MySQL before proceeding:
$ brew services stop mysql
$ brew services start mysql
To see available sevices:
$ brew services list
To check MySQL version:
$ mysql -V
Set root password
$ mysqladmin -u root password 'pass'
replace pass with you new password. To login:
$ mysql -u root -p
Then run the following commands:
mysql> create database fatwire;
mysql> use fatwire;
mysql> create table fatwire (id int(15), name varchar(255));
Leave this terminal open, and open a new terminal to the following steps.
Connection python3 to MySQL
Install PyMySQL as follows:
$ pip3 install pymsql
Then test connection with the following code (testconnection.py):
import pymysql.cursors
con = pymysql.connect(host = 'localhost', user = 'root', password = 'pass', db = 'fatwire', cursorclass = pymysql.cursors.DictCursor)
print('Connection Successfull');
To run:
$ python3 testconnection.py
Insert data into MySQL database using Python3
See the following code:
import pymysql.cursors
con = pymysql.connect(host = 'localhost', user = 'root', password = 'pass', db = 'fatwire', cursorclass = pymysql.cursors.DictCursor)
try:
cur = con.cursor()
a = 12331
b = 'The Nugget'
sql = "Insert into fatwire (id, name) " + " values (%s, %s)"
cur.execute(sql,(a, b))
con.commit()
finally:
con.close()