SQL - Python with MySQL

Jungmin·2022년 11월 10일
1

SQL

목록 보기
10/17
post-custom-banner

MySQL Driver 설치

pip install mysql-connector-python
import mysql.connector   #설치확인
  • 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.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********"
)
  • 사용 후엔 close() 꼭 해주기.
local.close()
remote.close()

✏ CREATE CONNECTION 예제 1

: Local MySQL의 zerobase 연결

local = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "********",
    database = "zerobase"
)
local.close()

✏ CREATE CONNECTION 예제 2

: AWS RDS (database-1)의 zerobase에 연결

remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)
remote.close()

⏹ SQL쿼리 실행

쿼리 실행하기 위한 코드

  • 테이블 생성
remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)

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    |       |
+----------+-------------+------+-----+---------+-------+
  • 테이블 삭제
remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)

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

SQL file 실행하기 위한 코드

  • test03.sql파일 생성
  • test03.sql 실행
remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)

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    |       |
+----------+-------------+------+-----+---------+-------+

SQL 파일 내 쿼리가 여러개 존재하는 경우

result = mycursor.execute(sql,multi=True)

  • test04.sql 생성
remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    password = "********",
    database = "zerobase"
)

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

레코드를 배열 형식으로 저장

- sql_file 테이블 조회 (읽어올 데이터 많은 경우 `buffered=True`)
remote = mysql.connector.connect(
    host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
    port = 3306,
    user = "admin",
    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()
01
01test01.sql
12test02.sql
23test03.sql
34test04.sql
profile
데이터분석 스터디노트🧐✍️
post-custom-banner

0개의 댓글