🔴Pyhon with MySQL

• Python으로 MySQL접속 후 사용

Install MySQL Driver

pip install mysql-connector-python

import mysql.connector

Create Connection

• MySQL에 접속하기 위한 코드

mydb = mysql.connector.connect(
	host = "<hostname>",
    user = "<username>",
    password = "<password>"
)

예제

• Local Database 연결

local = mysql.connector.Connect(
    host = "localhost",
    user = "root",
    password = "******"
)

• AWS RDS 연결

remote = mysql.connector.Connect(
    host = "database-1...com",
    port = 3306,
    user = "admin",
    password = "******"
)

Close Database

import mysql.connector

mydb = mysql.connector.connect(
	host = "<hostname>",
    user = "<username>",
    password = "<password>"
    database = "<databasename>"
)

mydb.close()

Connect to Database

• 특정 Database에 접속하기 위한 코드

import mysql.connector

mydb = mysql.connector.connect(
	host = "<hostname>",
    port = <port>,
    user = "<username>",
    password = "<password>"
    database = "<databasename>"
)

Execute SQL

• Query를 실행하기 위한 코드

import mysql.connector

mydb = mysql.connector.connect(
	host = "<hostname>",
    port = <port>,
    user = "<username>",
    password = "<password>"
    database = "<databasename>"
)

mycursor = mydb.cursor()
mycursor.execute(<query>);

예제 1

• 테이블 생성

remote = mysql.connector.Connect(
    host = "database-1...com",
    port = 3306,
    user = "admin",
    password = "******"
)

cur = remote.cursor()
cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")

remote.close()

mysql> desc sql_file;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| filename | varchar(16) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

예제 2

• 테이블 삭제

remote = mysql.connector.Connect(
    host = "database-1...com",
    port = 3306,
    user = "admin",
    password = "******"
)

cur = remote.cursor()
cur.execute("DROP TABLE sql_file")

remote.close()

mysql> desc sql_file;
ERROR 1146 (42S02): Table 'zerobase.sql_file' doesn't exist

Execute SQL File 1

• SQL File을 실행하기 위한 코드

mydb = mysql.connector.connect(
	host = "<hostname>",
    port = <port>,
    user = "<username>",
    password = "<password>"
    database = "<databasename>"
)

mycursor = mydb.cursor()

sql = open("<filename>.sql").read()
mycursor.execute(sql)

예제

• test03.sql 생성
  

• test03.sql 실행

remote = mysql.connector.Connect(
    host = "database-1...com",
    port = 3306,
    user = "admin",
    password = "******"
)

cur = remote.cursor()
sql = open("test03.sql").read()
cur.execute(sql)

remote.close()

mysql> desc sql_file;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| filename | varchar(16) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

Execute SQL File 2

• SQL File내에 Query가 여러개 존재하는 경우

mydb = mysql.connector.connect(
	host = "<hostname>",
    port = <port>,
    user = "<username>",
    password = "<password>"
    database = "<databasename>"
)

mycursor = mydb.cursor()

sql = open("<filename>.sql").read()
mycursor.execute(sql, multi=True)

예제

remote = mysql.connector.Connect(
    host = "database-1...com",
    port = 3306,
    user = "admin",
    password = "******"
)

cur = remote.cursor()
sql = open("test04.sql").read()
for result_iterator in cur.execute(sql, multi=True):
	if result_iterator.with_rows:
    	print(result_iterator.fetchall())
    else:
    	print(result_iterator.statement)

remote.commit()
remote.close()

mysql> select * from sql_file;
+------+------------+
| id   | filename   |
+------+------------+
|    1 | test01.sql |
|    2 | test02.sql |
|    3 | test03.sql |
|    4 | test04.sql |
+------+------------+

Fetch All

mycursor.execute(<query>)

result = mycursor.fetchall()
for data in result:
print(data)

예제

• sql_file 테이블 조회 (읽어올 데이터 양이 많은 경우 buffered=True)

remote = mysql.connector.Connect(
    host = "database-1....amazonaws.com",
    port = 3306,
    user = "admin",
    password = "******",
    database = "zerobase"
)

cur = remote.cursor(buffered=True)
cur.execute("SELECT * FROM sql_file")
result = cur.fetchall()
result

=>

[(1, 'test01.sql'), (2, 'test02.sql'), (3, 'test03.sql'), (4, 'test04.sql')]

for result_iterator in result:
    print(result_iterator)
    
=>

(1, 'test01.sql')
(2, 'test02.sql')
(3, 'test03.sql')
(4, 'test04.sql')

검색결과 Pandas로 읽기

import pandas as pd

df = pd.DataFrame(result)
df.head()

	0	1
0	1	test01.sql
1	2	test02.sql
2	3	test03.sql
3	4	test04.sql

🔴Python with CSV

• CSV에 있는 데이터를 Python으로 INSERT

Read CSV

• police_station.csv를 Pandas로 읽어와서 데이터 확인

import pandas as pd

df = pd.read_csv("police_station.csv")
df.tail()

	서울특별시경찰청	서울시 종로구 사직로8길 31
26	서울노원경찰서	서울특별시 노원구 노원로 283
27	서울방배경찰서	서울특별시 서초구 방배천로 54
28	서울도봉경찰서	서울특별시 도봉구 노해로 403
29	서울수서경찰서	서울특별시 강남구 개포로 617
30	서울은평경찰서	서울특별시 은평구 연서로 365

데이터베이스에 연결

import mysql.connector

conn = mysql.connector.connect(
	host = "",
    port = 3306,
    user = "",
    password ="",
    database = "zerobase"
)

Cursor 생성

cursor = conn.cursor(buffered=True)
sql = "INSERT INTO police_station VALUES(%s, %s)"

데이터 입력

• commit() : database에 적용하기 위한 명령어

for i, row in df.iterrows():
	cursor.execute(sql, tuple(row))
    print(tuple(row))
    conn.commit()
    
=>

('서울중부경찰서', '서울특별시 중구 수표로 27')
('서울종로경찰서', '서울특별시 종로구 율곡로 46')
('서울남대문경찰서', '서울특별시 중구 한강대로 410')
						.
                        .
                        .
('서울도봉경찰서', '서울특별시 도봉구 노해로 403')
('서울수서경찰서', '서울특별시 강남구 개포로 617')
('서울은평경찰서', '서울특별시 은평구 연서로 365')

결과 확인

cursor.execute("select * from police_station")

result = cursor.fetchall()
result

=>

[('서울중부경찰서', '서울특별시 중구 수표로 27'),
 ('서울종로경찰서', '서울특별시 종로구 율곡로 46'),
 ('서울남대문경찰서', '서울특별시 중구 한강대로 410'),
						.
                        .
                        .
 ('서울도봉경찰서', '서울특별시 도봉구 노해로 403'),
 ('서울수서경찰서', '서울특별시 강남구 개포로 617'),
 ('서울은평경찰서', '서울특별시 은평구 연서로 365')]

df = pd.DataFrame(result)
df.tail()

	0	1
26	서울노원경찰서	서울특별시 노원구 노원로 283
27	서울방배경찰서	서울특별시 서초구 방배천로 54
28	서울도봉경찰서	서울특별시 도봉구 노해로 403
29	서울수서경찰서	서울특별시 강남구 개포로 617
30	서울은평경찰서	서울특별시 은평구 연서로 365