[SQL] SQL 학습일지 02

8. DATABASE 연결

1. Local MYSQL 의 database 에 연결

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

local.close()

2. AWS RDS 의 databases 에 연결

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

remote.close()

9. SQL 실행

1. 테이블 생성
   
   

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

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

remote.close()

2. 테이블 삭제
   
   

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

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

remote.close()

3. SQL file 실행
   
   

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

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

remote.close()

4. SQL file 실행 (여러 query 존재시)
   
   

remote = mysql.connector.connect(
    host = "database-1.c9b0k6gtq6fn.ap-northeast-2.rds.amazonaws.com",
    port = 3306, 
    user = "admin",
    password = "zerobase",
    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()

정리

---

1. connector 생성
2. cursor ( 읽어올 데이터 양이 많으면 buffered=True 옵션 부여 )
3. execute 실행 ( 쿼리가 여러개면 multi=True 옵션 부여 )
4. fetchall( ) 로 결과 받기