Python with MySQL

---

Connect

---

Python으로 MySQL 접속 후 사용하는 방법

1. MySQL Driver 설치

pip install mysql-connector-python

2. 설치 확인

import mysql.connector

MySQL 접속

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

• Local Database 연결

# MySQL 연결
mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "********",
)

• AWS RDS (database-1) 연결

# AWS RDS 연결
mydb = mysql.connector.connect(
    host = "database-1.ca5oct2z6zq8.us-east-2.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********",
)

Close Database

---

• close() 구문 사용

mydb.close()

특정 Database에 접속

---

# MySQL 연결
mydb = mysql.connector.connect(
    host = "<hostname>",
    user = "<username>",
    password = "<password>",
		database = "<database>"
)

• Local database 접속

import mysql.connector

# MySQL 연결
mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "********",
		database = "zerobase"
)

EXECUTE

---

• Query를 실행하기 위한 코드

# MySQL 연결
mydb = mysql.connector.connect(
    host = "<hostname>",
    user = "<username>",
    password = "<password>",
		database = "<database>"
)

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

• [Ex] 테이블 생성

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

• [Ex] 테이블 삭제

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

SQL 파일 실행

---

# MySQL 연결
mydb = mysql.connector.connect(
    host = "<hostname>",
    user = "<username>",
    password = "<password>",
		database = "<database>"
)

mycursor.mydb.cursor()

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

• [Ex] 파일 실행

1. test03.sql 생성

2. test03. sql 실행

# MySQL 연결
remote = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "********",
    database = "zerobase"
)

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

remote.close()

3. 결과 확인

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

---

# MySQL 연결
remote = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "********",
    database = "zerobase"
)

cur = remote.cursor()
sql = open('test03.sql').read()
cur.execute(sql, multi = True)

remote.close()

• test04.sql 생성

INSERT INTO sql_file VALUES (1, 'test01.sql');
INSERT INTO sql_file VALUES (1, 'test02.sql');
INSERT INTO sql_file VALUES (1, 'test03.sql');
INSERT INTO sql_file VALUES (1, 'test04.sql');

• 실행

# MySQL 연결
remote = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "********",
    database = "zerobase"
)

cur = remote.cursor()
sql = open('test04.sql').read()

#cur.execute(sql, multi = True)

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()

Fectch all

---

mycursor.execute(<query>)

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

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

remote = mysql.connector.connect(
    host = 'localhost',
    port = 3306,
    user = 'root',
    password = '********',
    database = 'zerobase'
)

cur = remote.cursor(buffered = True)
cur.execute('SELECT * FROM sql_file')

result = cur.fetchall()

for result_iterator in result:
    print(result_iterator)

remote.close()

• 검색결과를 Pandas로 읽기

import pandas as pd

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

• [Ex] celeb 테이블 조회
  

remote = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    password = '********',
    database = 'zerobase'
)

cur = remote.cursor(buffered = True)
cur.execute('SELECT * FROM celeb')

result = cur.fetchall()

for result_iterator in result:
    print(result_iterator)

remote.close()