pip install mysql-connector-python
import mysql.connector #설치확인
mydb = mysql.connector.connect(
host = "hostname",
user = "username",
password = "password"
)
local = mysql.connector.connect(
host = "localhost",
user = "root",
password = "********"
)
remote = mysql.connector.connect(
host = "database-1.cyxirxsmjvie.ap-northeast-1.rds.amazonaws.com",
port = 3306,
user = "admin",
password = "********"
)
local.close()
remote.close()
: Local MySQL의 zerobase 연결
local = mysql.connector.connect(
host = "localhost",
user = "root",
password = "********",
database = "zerobase"
)
local.close()
: 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()
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
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 | |
+----------+-------------+------+-----+---------+-------+
result = mycursor.execute(sql,multi=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()
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 |
+------+------------+
레코드를 배열 형식으로 저장
- 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()
import pandas as pd
df = pd.DataFrame(result)
df.head()
0 | 1 | |
---|---|---|
0 | 1 | test01.sql |
1 | 2 | test02.sql |
2 | 3 | test03.sql |
3 | 4 | test04.sql |