mysqldump --set-gtid-purged=OFF -h "database-1.ctr6kyvctlhm.ap-southeast-2.rds.amazonaws.com" -P 3306 -u admin -p zerobase police_station > backup_police.sql
mysql > use zerobase;
mysql > delete from police_station;
pip install mysql-connector-python
import mysql.connector
mydb = mysql.connector.connect(
host = "<hostname>",
user = "<username>",
password = "<password>"
)
# Local Database 연결
local = mysql.connector.connect(
host = "localhost",
user = "root",
password = "*********"
)
# AWS RDS (database-1) 연결
remote = mysql.connector.connect(
host = "database-1.ctr6kyvctlhm.ap-southeast-2.rds.amazonaws.com",
port = 3306,
user = "admin",
password = "*********"
)
local.close()
remote.close()
import mysql.connector
mydb = mysql.connector.connect(
host = "<hostname>",
port = <port>,
user = "<username>",
password = "<password>",
database = "<databasename>"
)
import mysql.connector
mydb = mysql.connector.connect(
host = "<hostname>",
user = "<username>",
password = "<password>",
database = "<databasename>"
)
mycursor = mydb.cursor()
mycursor.execute(<query>);
cur = remote.cursor()
cur.execute("CREATE TABLE sql_file (id int, filename varchar(16))")
remote.close()
-> 쿼리를 실행해서 테이블 생성
cur = remote.cursor()
cur.execute("DROP TABLE sql_file")
remote.close()
-> 쿼리를 실행해서 테이블 삭제
mydb = mysql.connector.connect(
host = "<hostname>",
user = "<username>",
password = "<password>",
database = "<databasename>"
)
mycursor = mydb.cursor()
sql = open("<filename>.sql").read()
mycursor.execute(sql)
cur = remote.cursor()
sql = open("test03.sql").read()
cur.execute(sql)
remote.close()
mycursor = mydb.cursor()
sql = oepn("<filename>.sql").read()
result = mycursor.execute(sql, multi=True)
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()
-> 쿼리를 직접 입력해서 실행시켜도 되고, 쿼리를 파일로 만들어서 파일을 실행시켜도 됨
mycursor.execute(<query>)
result = mycursor.fetchall()
for data in result:
print(data)
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, 'test01.sql')
(2, 'test02.sql')
(3, 'test03.sql')
(4, 'test04.sql')
import pandas as pd
df = pd.DataFrame(result)
df.head()
for i, row in df.iterrows():
cursor.execute(sql, tuple(row))
print(tuple(row))
conn.commit()
-> commit() : database에 적용하기 위한 명령
cursor.execute("SELECT * FROM police_station")
result = cursor.fetchall()
for row in result:
print(row)
df = pd.DataFrame(result)
df
import pandas as pd
df = pd.read_csv('2020_crime.csv', encoding='euc-kr')
df.head()
<혼자서 해보기>
(1)
import mysql.connector
conn = mysql.connector.connect(
host = "database-1.ctr6kyvctlhm.ap-southeast-2.rds.amazonaws.com",
port = 3306,
user = "admin",
password = "minji980603",
database = "zerobase"
)
(2) 데이터 파악하고, 그에 맞게 cctv 테이블 만들기
import pandas as pd
df = pd.read_csv("Seoul_CCTV.csv", encoding='utf-8')
df.head()
sql = "create table cctv (기관명 varchar(8), 소계 int, 2013년도이전 int, 2014년 int, 2015년 int, 2016년 int)"
cursor = conn.cursor(buffered=True)
cursor.execute(sql)
(3) cctv 테이블에 데이터 넣기
sql = "insert into cctv values (%s, %s, %s, %s, %s, %s)"
cursor = conn.cursor(buffered=True)
for i, row in df.iterrows():
cursor.execute(sql, tuple(row))
print(tuple(row))
conn.commit()
(4) cctv 테이블의 데이터 조회하기
cursor.execute("select * from cctv")
result = cursor.fetchall()
for row in result:
print(row)
(5) 조회한 데이터를 pandas로 변환하여 출력하기
df = pd.DataFrame(result)
df.head()
테이블의 각 레코드를 식별하기 위함
중복되지 않은 고유값을 포함
NULL 값을 포함할 수 없음
테이블 당 하나의 기본키를 가짐
Primary Key 생성 문법
CREATE TABLE tablename
(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
...
CONSTRAINT constraint_name # constraint는 생략 가능
PRIMARY KEY (column1, column2, ...)
);
ALTER TABLE tablename
DROP PRIMARY KEY;
ALTER TABLE tablename
ADD PRIMARY KEY (column1, column2, ...)
CREATE TABLE tablename
(
column1 datatype NOT NULL,
column2 datatype NOT NULL,
column3 datatype,
column4 datatype,
...
CONSTRAINT constraint_name
PRIMARY KEY (column1, column2, ...),
CONSTRAINT constraint_name
FOREIGN KEY (column3, column4, ...) REFERENCES REF_tablename(REF_column)
);
CREATE TABLE orders
(
oid int NOT NULL,
order_no varchar(16),
pid int,
PRIMARY KEY (oid),
CONSTRAINT FK_person FOREIGN KEY (pid) REFERENCES person(pid)
);
-> order 테이블의 pid(외래키)는 person 테이블의 pid를 참조함
CREATE TABLE job
(
jid int NOT NULL,
name varchar(16),
pid int,
PRIMARY KEY (jid),
FOREIGN KEY (pid) REFERENCES person(pid)
);
SHOW CREATE TABLE tablename;
ALTER TABLE tablename
DROP FOREIGN KEY constraint_name;
ALTER TABLE tablename
ADD FOREIGN KEY (column) REFERENCES REF_tablename(REF_column);
<제로베이스 데이터 취업 스쿨>