How to use pymysql and mysql

In [10]:

import pymysql, argparse, os, sys
from sqlalchemy import create_engine
import pandas as pd

How to use pymysql and mysql

Step 0. install mysql, pymysql and setting mysql to use remote connection

Version information

• Unbuntu 16.04

• $ mysql --version 
  

  mysql Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using EditLine wrapper

See this document if you want more details. korean document

1.Install mysql

$ sudo apt-get update
$ sudo apt-get install mysql-server

2.Mysql configuration settings

If you install mysql server, default settings allows that

• ip: 127.0.0.1
• port: 3306

On Ubuntu 16, the path is typically /etc/mysql/mysql.conf.d/mysqld.cnf.
Edit this file as follows. (we allow remote connection to mysql)

" /etc/mysql/mysql.conf.d/mysqld.cnf ... "
# localhost which is more compatible and is not less secure.                                                 
bind-address    = 0.0.0.0                                                                                    
#bind-address   = 127.0.0.1  

3.Granting Access to a User from a Remote Machine

The next step is to allow access to the database to the remote user.

$ mysql -u root -p
mysql > SELECT Host, User  FROM mysql.user;    
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | root             |
| ...       | ...              |

To create a new MySQL user account, run the following command:

mysql > CREATE USER '<USERNAME>'@'localhost' IDENTIFIED BY '<PASSWORD>';

To grant access from another host, change the hostname part (localhost) with the remote machine IP. For example, to grant access from a machine with IP 10.8.0.5 you would run:

mysql > CREATE USER '<USERNAME>'@'10.8.0.5' IDENTIFIED BY '<PASSWORD>';

To create a user that can connect from any host, use the % wildcard as a host part:

CREATE USER '<USERNAME>'@'%' IDENTIFIED BY '<PASSWORD>';

If you want to more details about how to deal with granting previleges to a MySQL Account, see this document, Grant Privileges to a MySQL User Account part

4.Configuring Firewall

The last step is to configure your firewall to allow traffic on port 3306 (MySQL default port) from the remote machine.

$ sudo ufw allow 3306

5. restart

$ service mysql restart

6. remote connection

If you want to connect mysql server remotely from another computer, enter this command as follows.
Fill up with <..> as custom settings.

$ mysql -h<IP> -u<ID> -p<PASSWORD>

Step 1. Get argument

In [6]:

parser = argparse.ArgumentParser()
parser.add_argument('-user', help="mysql database user", type=str, required=False, default='*****')
parser.add_argument('-pw', help="password", type=str, required=False, default='****')
parser.add_argument('-host', help="ip address", type=str, required=False, default='***.***.***.***')
parser.add_argument('-db', help="database name", type=str, required=False, default='classicmodel')
parser.add_argument('-charset', help="character set to use", type=str, required=False, default='utf8mb4')
parser.add_argument('-table', help="table name", type=str, required=False, default='*****')
sys.argv = ['-f']
args = parser.parse_args()

In [7]:

args

Namespace(charset='utf8mb4', db='classicmodel', host='***.***.***.***', pw='****', table='*****', user='*****')

Step2. Connect

In [8]:

con = pymysql.connect(host=args.host, user=args.user, password=args.pw, use_unicode=True, charset=args.charset)
cursor = con.cursor()

In [15]:

## helper function
sql = lambda command: pd.read_sql(command, con)
def fetch(command):
    cursor.execute(command)
    return cursor.fetchall()

In [16]:

sql("show databases")

	Database
0	information_schema
1	classicmodels
2	daml
3	mysql
4	performance_schema
5	sys

In [20]:

fetch("use classicmodels")

()

In [21]:

sql("show tables")

	Tables_in_classicmodels
0	customers
1	employees
2	offices
3	orderdetails
4	orders
5	payments
6	productlines
7	products

In [22]:

sql("select * from customers")

	customerNumber	customerName	contactLastName	contactFirstName	phone	addressLine1	addressLine2	city	state	postalCode	country	salesRepEmployeeNumber	creditLimit
0	103	Atelier graphique	Schmitt	Carine	40.32.2555	54, rue Royale	None	Nantes	None	44000	France	1370.0	21000.0
1	112	Signal Gift Stores	King	Jean	7025551838	8489 Strong St.	None	Las Vegas	NV	83030	USA	1166.0	71800.0
2	114	Australian Collectors, Co.	Ferguson	Peter	03 9520 4555	636 St Kilda Road	Level 3	Melbourne	Victoria	3004	Australia	1611.0	117300.0
3	119	La Rochelle Gifts	Labrune	Janine	40.67.8555	67, rue des Cinquante Otages	None	Nantes	None	44000	France	1370.0	118200.0
4	121	Baane Mini Imports	Bergulfsen	Jonas	07-98 9555	Erling Skakkes gate 78	None	Stavern	None	4110	Norway	1504.0	81700.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...
117	486	Motor Mint Distributors Inc.	Salazar	Rosa	2155559857	11328 Douglas Av.	None	Philadelphia	PA	71270	USA	1323.0	72600.0
118	487	Signal Collectibles Ltd.	Taylor	Sue	4155554312	2793 Furth Circle	None	Brisbane	CA	94217	USA	1165.0	60300.0
119	489	Double Decker Gift Stores, Ltd	Smith	Thomas	(171) 555-7555	120 Hanover Sq.	None	London	None	WA1 1DP	UK	1501.0	43300.0
120	495	Diecast Collectables	Franco	Valarie	6175552555	6251 Ingle Ln.	None	Boston	MA	51003	USA	1188.0	85100.0
121	496	Kelly's Gift Shop	Snowden	Tony	+64 9 5555500	Arenales 1938 3'A'	None	Auckland	None	None	New Zealand	1612.0	110000.0

122 rows × 13 columns

In [24]:

data = fetch("select city from customers")
data[:10]

(('Nantes',),
 ('Las Vegas',),
 ('Melbourne',),
 ('Nantes',),
 ('Stavern',),
 ('San Rafael',),
 ('Warszawa',),
 ('Frankfurt',),
 ('San Francisco',),
 ('NYC',))