Python with MySQL

MySQL 접속 코드

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

AWS RDS 연결

remote = mysql.connector.connect(
	host = "database-1.cmd9bc4iknbs.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "cjaeks6401",
    password = "********"
)

• 끝나면 connection을 받아온 변수를 close 해야 함

Connect to Database

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

AWS RDS Database 연결

remote = mysql.connector.connect(
	host = "database-1.cmd9bc4iknbs.ap-southeast-2.rds.amazonaws.com",
    port = 3306,
    user = "cjaeks6401",
    password = "********",
    database = "zerobase"
)

Query를 실행 코드

import mysql.connector
mydb = mysql.connector.connect(
	host = "<hostname>",
    user = "<username>",
	password = "<password>",
    database = "<databasename>"
)	
mycursor = mydb.cursor()
mycursor.execute(<query>);

SQL File 실행 코드

mydb = mysql.connector.connect(
	host = "<hostname>",
    user = "<username>",
	password = "<password>",
    database = "<databasename>"
)	
mycursor = mydb.cursor()
sql = open("<filename>.sql").read()
mycursor.execute(sql)

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

mydb = mysql.connector.connect(
	host = "<hostname>",
    user = "<username>",
	password = "<password>",
    database = "<databasename>"
)	
mycursor = mydb.cursor()
sql = open("<filename>.sql").read()
result = mycursor.execute(sql, multi=True)

Fetch All

mycursor.execute(<query>)
result = mycursor.fetchall()
for data in result:
	print(data)
- buffered=True : 읽어올 데이터 양이 많은 경우 작성

Pandas with CSV

실습

자료출처 : 제로베이스 데이터스쿨