D-21-SQL with Python

1. Fetch All

cursor = conn.cursor(buffered=True)
cursor.execute('select * from crime_status')

result = cursor.fetchall()
for row in result:
  print(row)

2. buffered = True
   : 읽어올 데이터 양이 많을 경우 설정

cur = remote.cursor(buffered=True)

3. pandas csv read 시 한글 깨지는 경우

import pandas as pd
df = pd.read_csv('2020_crime.csv',encoding='euc-kr')
df.head()

4. pandas db 데이터 insert

sql = """insert into crime_status values("2020",%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()

5. db 데이터 조회

sql = 'select * from cctv'

cursor = conn.cursor(buffered = True)
cursor.execute(sql)
result = cursor.fetchall()

for row in result:
  print(row)