Python with MySQL - execute

Execute SQL

remote = mysql.connector.connect(
host = "",
port = ,
user = "",
password = "",
database = ""
)

cur = remote.cursor()
cur.execute("query")

remote.close()

테이블 생성

remote = mysql.connector.connect(
	host = "",
    port = ,
    user = "",
    password = "",
    database = ""
)

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

remote.close()

SQL File 실행

• query가 한개

mydb = mysql.connector.connect(
	host = "",
    port = ,
    user = "",
    password = "",
    database = ""
)

mycursor = mydb.cursor()

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

remote.close()

• query가 여러개 -> multi = True 옵션 추가

# for문이 없으면 오류는 안 나도 결과가 제대로 안 나옴
remote = mysql.connector.connect(
	host = "",
    port = ,
    user = "",
    password = "",
    database = ""
)

cur = remote.cursor()
sql = open("filename.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()

Fetch All

cur.execute("query")

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

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

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

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

result = cur.fetchall()
for result_iterator in result:
	print(result_iterator)

remote.close()