3T_데이터베이스, 테이블 생성하고 데이터 추가하기

In [1]:

    

zigbang_df = pd.read_csv("zigbang.csv")


    

In [2]:

    

zigbang_df.head()


    

    
Out[2]:






  

    

      

      
직방 매물번호
      
연락처
      
이메일
      
부동산
      
보증금
      
월세
    
  
  

    

      
0
      
3467204.0
      
010-4780-3740
      
서울특별시 강남구 논현1동 124-33
      
TODAY공인중개사(이인규)
      
1000.0
      
100.0
    
    

      
1
      
3150497.0
      
010-4780-3740
      
서울특별시 강남구 논현1동 124-33
      
TODAY공인중개사(이인규)
      
11000.0
      
60.0
    
    

      
2
      
3440906.0
      
010-9134-5291
      
서울특별시 강남구 신사동 610-2 1층
      
열린공인중개사(강광수)
      
5000.0
      
65.0
    
    

      
3
      
3376834.0
      
010-4166-1970
      
서울특별시 강남구 논현동 108 웰스톤상가 B101
      
미래공인중개사(김유철)
      
3000.0
      
95.0
    
    

      
4
      
3331511.0
      
010-3015-2251
      
서울특별시 강남구 역삼동 696-10 1층
      
한백부동산공인중개사(김남조)
      
110.0
      
110.0
    
  

In [3]:

    

import pymysql


    

In [15]:

    

db = pymysql.connect(
    "db.fastcamp.us",
    "root",
    "dkstncks",
#     "sakila",
    charset="utf8",
)


    

In [16]:

    

# cursor 라는 객체를 가져와서 DB에 명령을 실행시킵니다.
cursor = db.cursor()


    

In [17]:

    

# 현재 있는 모든 데이터베이스 이름을 가져오는 명령어
SQL_QUERY = """
    SHOW DATABASES;
"""

# pd.read_sql(SQL_QUERY, db)  => Pandas의 DataFrame으로 만들어주는 명령어. 이렇게 해도 되지만 cursor를 이용해보자
cursor.execute(SQL_QUERY)
cursor.fetchall()


    

    
Out[17]:





(('information_schema',),
 ('JAYWHY',),
 ('Jeonjonghee',),
 ('JessieB',),
 ('KBO_salary',),
 ('RRBB',),
 ('SJLEE',),
 ('angie0829',),
 ('ansuchan',),
 ('beomheepark',),
 ('bright_sample',),
 ('bright_sample_Day2',),
 ('byeon',),
 ('byeon2',),
 ('chibok_sample2',),
 ('choyonghwan',),
 ('dabang',),
 ('dobestan',),
 ('dobestan_sample',),
 ('doojin',),
 ('eunjung',),
 ('eunseop_sample',),
 ('goldstar_sample',),
 ('gs_sample',),
 ('hansulgi',),
 ('higee_sample',),
 ('insurance',),
 ('jeonjonghee',),
 ('jkeun_sample',),
 ('jones_sample',),
 ('jonghee',),
 ('jooyoungsuk',),
 ('junhwan',),
 ('kbo',),
 ('kcchoi_resample',),
 ('khyun',),
 ('kimkipoy',),
 ('kipoy',),
 ('kty_sample',),
 ('leo',),
 ('minsoo',),
 ('movie',),
 ('mysql',),
 ('ngmtnpython',),
 ('obolosample',),
 ('performance_schema',),
 ('sakila',),
 ('sechang_sample',),
 ('seongjinkim',),
 ('sibadog_sample',),
 ('songingeun',),
 ('sunghwan_sample',),
 ('tada',),
 ('world',),
 ('yanghwi_sample',),
 ('yoonjin',),
 ('yudaesung_sample',))

In [18]:

    

#데이터베이스 생성(=="world", "sakila", ...)
SQL_QUERY = """
    CREATE DATABASE kipoy;
"""
cursor.execute(SQL_QUERY)


    

    




---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-18-13c87cbc3282> in <module>()
      3     CREATE DATABASE kipoy;
      4 """
----> 5 cursor.execute(SQL_QUERY)

C:\Anaconda3\lib\site-packages\pymysql\cursors.py in execute(self, query, args)
    164         query = self.mogrify(query, args)
    165 
--> 166         result = self._query(query)
    167         self._executed = query
    168         return result

C:\Anaconda3\lib\site-packages\pymysql\cursors.py in _query(self, q)
    320         conn = self._get_db()
    321         self._last_executed = q
--> 322         conn.query(q)
    323         self._do_get_result()
    324         return self.rowcount

C:\Anaconda3\lib\site-packages\pymysql\connections.py in query(self, sql, unbuffered)
    835                 sql = sql.encode(self.encoding, 'surrogateescape')
    836         self._execute_command(COMMAND.COM_QUERY, sql)
--> 837         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    838         return self._affected_rows
    839 

C:\Anaconda3\lib\site-packages\pymysql\connections.py in _read_query_result(self, unbuffered)
   1019         else:
   1020             result = MySQLResult(self)
-> 1021             result.read()
   1022         self._result = result
   1023         if result.server_status is not None:

C:\Anaconda3\lib\site-packages\pymysql\connections.py in read(self)
   1302     def read(self):
   1303         try:
-> 1304             first_packet = self.connection._read_packet()
   1305 
   1306             if first_packet.is_ok_packet():

C:\Anaconda3\lib\site-packages\pymysql\connections.py in _read_packet(self, packet_type)
    981 
    982         packet = packet_type(buff, self.encoding)
--> 983         packet.check_error()
    984         return packet
    985 

C:\Anaconda3\lib\site-packages\pymysql\connections.py in check_error(self)
    393             errno = self.read_uint16()
    394             if DEBUG: print("errno =", errno)
--> 395             err.raise_mysql_exception(self._data)
    396 
    397     def dump(self):

C:\Anaconda3\lib\site-packages\pymysql\err.py in raise_mysql_exception(data)
    100     errval = data[4:].decode('utf-8', 'replace')
    101     errorclass = error_map.get(errno, InternalError)
--> 102     raise errorclass(errno, errval)

ProgrammingError: (1007, "HY000Can't create database 'kipoy'; database exists")

In [19]:

    

pd.read_sql("SHOW DATABASES;", db)


    

    
Out[19]:






  

    

      

      
Database
    
  
  

    

      
0
      
information_schema
    
    

      
1
      
JAYWHY
    
    

      
2
      
Jeonjonghee
    
    

      
3
      
JessieB
    
    

      
4
      
KBO_salary
    
    

      
5
      
RRBB
    
    

      
6
      
SJLEE
    
    

      
7
      
angie0829
    
    

      
8
      
ansuchan
    
    

      
9
      
beomheepark
    
    

      
10
      
bright_sample
    
    

      
11
      
bright_sample_Day2
    
    

      
12
      
byeon
    
    

      
13
      
byeon2
    
    

      
14
      
chibok_sample2
    
    

      
15
      
choyonghwan
    
    

      
16
      
dabang
    
    

      
17
      
dobestan
    
    

      
18
      
dobestan_sample
    
    

      
19
      
doojin
    
    

      
20
      
eunjung
    
    

      
21
      
eunseop_sample
    
    

      
22
      
goldstar_sample
    
    

      
23
      
gs_sample
    
    

      
24
      
hansulgi
    
    

      
25
      
higee_sample
    
    

      
26
      
insurance
    
    

      
27
      
jeonjonghee
    
    

      
28
      
jkeun_sample
    
    

      
29
      
jones_sample
    
    

      
30
      
jonghee
    
    

      
31
      
jooyoungsuk
    
    

      
32
      
junhwan
    
    

      
33
      
kbo
    
    

      
34
      
kcchoi_resample
    
    

      
35
      
khyun
    
    

      
36
      
kimkipoy
    
    

      
37
      
kipoy
    
    

      
38
      
kty_sample
    
    

      
39
      
leo
    
    

      
40
      
minsoo
    
    

      
41
      
movie
    
    

      
42
      
mysql
    
    

      
43
      
ngmtnpython
    
    

      
44
      
obolosample
    
    

      
45
      
performance_schema
    
    

      
46
      
sakila
    
    

      
47
      
sechang_sample
    
    

      
48
      
seongjinkim
    
    

      
49
      
sibadog_sample
    
    

      
50
      
songingeun
    
    

      
51
      
sunghwan_sample
    
    

      
52
      
tada
    
    

      
53
      
world
    
    

      
54
      
yanghwi_sample
    
    

      
55
      
yoonjin
    
    

      
56
      
yudaesung_sample
    
  

In [20]:

    

db.commit()  # 만약 접속이 안 되면 이거 실행하고 해라.


    

In [21]:

    

db = pymysql.connect(
    "db.fastcamp.us",
    "root",
    "dkstncks",
    "kipoy",
    charset="utf8",
)


    

In [22]:

    

# SHOW DATABASES;
# 접근한 이후 ( USE _______; ) => SHOW TABLES;
SQL_QUERY = """
    SHOW TABLES;
"""
pd.read_sql(SQL_QUERY, db)


    

    
Out[22]:






  

    

      

      
Tables_in_kipoy
    
  
  

    

      
0
      
agency
    
    

      
1
      
provider
    
  

In [23]:

    

# 1. 데이터베이스 연결
SQL_QUERY = """
    USE kipoy;
"""
cursor.execute(SQL_QUERY)

# 2. 기존의 데이터베이스 제거
SQL_QUERY = """
    DROP TABLE IF EXISTS provider;
"""
cursor.execute(SQL_QUERY)

# 3. 테이블 생성하기
SQL_QUERY = """
    CREATE TABLE IF NOT EXISTS provider
    (
        provider_id int PRIMARY KEY,
        name varchar(20)
    )
    ;
"""
cursor.execute(SQL_QUERY)
db.commit()


    

In [24]:

    

SQL_QUERY = """
    SELECT *
    FROM provider
    ;
"""

pd.read_sql(SQL_QUERY, db)


    

    
Out[24]:






  

    

      

      
provider_id
      
name
    
  
  

  

In [27]:

    

SQL_QUERY = """
    INSERT INTO provider (provider_id, name)
    VALUES (
        1,
        "다방"
    );
"""
cursor.execute(SQL_QUERY)
db.commit()


    

In [28]:

    

pd.read_sql("SELECT * FROM provider;", db)   #받아오는 데 시간이 꽤 걸리네


    

    
Out[28]:






  

    

      

      
provider_id
      
name
    
  
  

  

In [29]:

    

db.commit()


    

In [30]:

    

SQL_QUERY = """
    DROP TABLE IF EXISTS agency;
"""
cursor.execute(SQL_QUERY)


    

    
Out[30]:





0

In [31]:

    

SQL_QUERY = """
    CREATE TABLE IF NOT EXISTS agency (
        agency_id int,
        provider_id int,
        name varchar(100),
        phonenumber varchar(20)
    )
    ;
"""
cursor.execute(SQL_QUERY)


    

    
Out[31]:





0

In [13]:

    

db.commit()


    

In [32]:

    

pd.read_sql("SELECT * FROM agency;", db)


    

    
Out[32]:






  

    

      

      
agency_id
      
provider_id
      
name
      
phonenumber
    
  
  

  

In [33]:

    

SQL_QUERY = """
    INSERT INTO agency (agency_id, provider_id, name, phonenumber)
    VALUES (
        4,
        2,
        "해퓌 부동산",
        "010-6235-3317"
    )
    ;
"""
cursor.execute(SQL_QUERY)
db.commit()


    

In [34]:

    

pd.read_sql("SELECT * FROM agency;", db)


    

    
Out[34]:






  

    

      

      
agency_id
      
provider_id
      
name
      
phonenumber
    
  
  

  

In [45]:

    

SQL_QUERY = """
    SELECT *
    FROM agency a
        JOIN provider p
        ON a.provider_id = p.provider_id
    ;
"""
pd.read_sql(SQL_QUERY, db)


    

    
Out[45]:






  

    

      

      
agency_id
      
provider_id
      
name
      
phonenumber
      
provider_id
      
name