#local DB와 pandas
import sqlite3
import pandas as pd
sql="create table if not exists test(product varchar(10), maker varchar(10), weight real, price integer)"
conn=sqlite3.connect(':memory:') #db 안만들고 휘발성 메모리에서 하는 것
#conn=sqlite3.connect('mydb') , db 만들어서 하기
conn.execute(sql)
conn.commit()
datas=[('mouse','samsong', 12.5, 5000),('keyboard','alg', 50.5, 35000)]
stmt="insert into test values(?,?,?,?)"
conn.executemany(stmt, datas) #한번에 때려넣을 땐 익스큐트매니
data1=('monitor', 'abc', 100.0, 550000)
conn.execute(stmt, data1)
conn.commit()
#읽어오기
cursor = conn.execute("select * from test")
rows = cursor.fetchall()
for a in rows:
print(a)
'''
('mouse', 'samsong', 12.5, 5000)
('keyboard', 'alg', 50.5, 35000)
('monitor', 'abc', 100.0, 550000)
'''
# DataFrame에 자료를 기억
df1=pd.DataFrame(rows, columns=['product','maker','weight','price'])
print(df1)
print()
# pandas의 SQL 기능을 사용
df2 = pd.read_sql('select * from test',conn)
print(df2)
print()
# DataFrame의 자료를 DB로 저장
data = {
'product' : ['연필','볼펜', '지우개'],
'maker' : ['모나미', '모나미', '모나미'],
'weight' : [2.3, 5.5, 12.0],
'price' : [500, 1500, 1000]
}
frame = pd.DataFrame(data) # data를 DataFrame으로 만든다.
print(frame)
print('~~~~~~~')
frame.to_sql('test', conn, if_exists='append',index=False) # frame data를 sqlite에 넣기
df3 = pd.read_sql('select * from test',conn)
print(df3)
print()
print(pd.read_sql('select count(*) as 건수 from test', conn)) # 행의 갯수를 호출
<console>
('mouse', 'samsong', 12.5, 5000)
('keyboard', 'alg', 50.5, 35000)
('monitor', 'abc', 100.0, 550000)
product maker weight price
0 mouse samsong 12.5 5000
1 keyboard alg 50.5 35000
2 monitor abc 100.0 550000
product maker weight price
0 mouse samsong 12.5 5000
1 keyboard alg 50.5 35000
2 monitor abc 100.0 550000
product maker weight price
0 연필 모나미 2.3 500
1 볼펜 모나미 5.5 1500
2 지우개 모나미 12.0 1000
~~~~~~~
product maker weight price
0 mouse samsong 12.5 5000
1 keyboard alg 50.5 35000
2 monitor abc 100.0 550000
3 연필 모나미 2.3 500
4 볼펜 모나미 5.5 1500
5 지우개 모나미 12.0 1000
건수
0 6