[Python] SqlLite를 이용한 Create Table 및 Pandas 출력

*본 포스팅은 SLiPP 스터디를 진행하면서 위키 작성을 위해 작성하였습니다. SLiPP 충성충성~^^7 

version

python 3.6

pip 18

시작전에

pip install sqlite

pip install pandas

pip install jupyter

jupyter notebook 을 이용해 작성하였습다

---

Table order_list

order_date	text	yyyyMMdd
order_type	number	주문타입 (매수 = '1' / 매도 = '2')
item_code	text	품번
hoga	text	호가 (지정가: '00', 시장가: '03')
count qauntity	text	수량
price	text	가격
status	number	상태 (주문 전 : '0' , 주문완료 : '1')

* count 를 컬럼명으로 썻더니 dataframe의 count와 겹치더라.. 바꾸자 

소스에는 적용 안되있던둡

---

create_table.py

import sqlite3

con  = sqlite3.connect("C:/PROJECT/../conn.db")

cursor = con.cursor()

cursor.execute("CREATE TABLE order_list(order_date text,order_type number, item_code text, hoga text, count text, price text, status number)")

cursor.execute("insert into order_list values ('20181106',1,'035720','03','10','0',0)")

cursor.execute("insert into order_list values ('20181106',1,'039490','03','10','0',0)")

cursor.execute("insert into order_list values ('20181106',1,'035720','03','10','0',0)")

cursor.execute("insert into order_list values ('20181106',2,'035720','03','10','0',0)")

con.commit()

con.close()

create_table.py

#코드에는 이 테이블을 적용하지 않았지만, 만들어는 봄

import sqlite3

con  = sqlite3.connect("C:/PROJECT/1_OJT_ENM/문서/TESt/conn.db")

cursor = con.cursor()

cursor.execute("CREATE TABLE common_code(  main_ctg text, second_ctg text, code_name text)")

cursor.execute("insert into common_code values ('order_type','1','매수')")

cursor.execute("insert into common_code values ('order_type','2','매도')")

cursor.execute("insert into common_code values ('hoga','01','호가')")

cursor.execute("insert into common_code values ('hoga','03','시장가')")

cursor.execute("insert into common_code values ('status','1','주문완료')")

cursor.execute("insert into common_code values ('status','0','주문전')")

con.commit()

con.close()

#"SELECT order_date

, (select code_name from common_code where main_ctg = 'order_type' and second_ctg = order_type) as order_type 

, item_code

, (select code_name from common_code where main_ctg = 'hoga' and second_ctg = hoga) as hoga

, count

, (select code_name from common_code where main_ctg = 'status' and second_ctg = status) as status 

from order_list

---

show_table.py

import sqlite3
import pandas as pd
from pandas import Series, DataFrame

con  = sqlite3.connect("C:/Users/eunbi/pyTrader/conn.db")

#1. cursor fetch 이용
cursor = con.cursor()
cursor.execute("SELECT * FROM order_list")



cursor.fetchone()     #한 row만 가져옴
cursor.fetchall()        # fetchOne 이후 나머지 row들을 가져옴

cursor.execute("SELECT * FROM order_list")
cursor.fetchall()         # 나머지 row들을 가져옴

#pandas dataframe 이용
df = pd.read_sql("SELECT * FROM order_list", con, index_col=None)

print(df)
print(df.loc[0])        # i번째 index를 가져옴

count_row = df.shape[0]  # gives number of row count
acount_col = df.shape[1]  # gives number of col count

for d in df.iterrows():
    #d[0] = index number
    print(d[1].order_type)
    print("\n=================")

트레이딩 시스템 함수 변경

def readSQLite(self):
    con = sqlite3.connect("C:/Users/eunbi/pyTrader/conn.db")
    df = pd.read_sql("SELECT * FROM order_list", con, index_col=None)
    print(df)

    #row Count 설정
    row_count = df.shape[0]
    self.tableWidget_3.setRowCount(row_count)

    # read sql
    for i in range(0, df.shape[0]):
        for j in range(0, df.shape[1]):
            # 종목명 출력
            item = QTableWidgetItem(self.getCodeName(str(df.loc[i][j]), j))
            item.setTextAlignment(Qt.AlignVCenter | Qt.AlignCenter)
            self.tableWidget_3.setItem(i, j, item)

    # resizing
    self.tableWidget_3.resizeRowsToContents()
    con.commit()
    con.close()

Code를...바꾸고자....... 

쿼리로 처리했으면 좋겟지만,

테이블이 추가로 필요해서 일단 하드코딩

def getCodeName(self, code, index):
    dic = [None  # order_date
        , {'1': '매수', '2': '매도'}  # order_type
        , 'ItemCode'  # item_code
        , {'03': '시장가', '01': '호가'}  # hoga
        , None  # quantity
        , None  # price
        , {'0': '주문안함', '1': '주문완료'}]  # status

    if dic[index] == 'ItemCode':
        return self.kiwoom.get_master_code_name(code)

    if dic[index] == None:
        return str(code)
    else:
        return dic[index][code]

* 주문 

def trade_stock_sqlite(self):
    # account
    account = self.comboBox.currentText()

    #db 연동
    con = sqlite3.connect("C:/Users/eunbi/pyTrader/conn.db")
    cursor = con.cursor()
    df = pd.read_sql("SELECT * FROM order_list", con, index_col=None)

    for d in df.iterrows():
        # 1= true(매수완료), 0 = false(매수안함)
        # 1일땐 매수 완료이므로 pass
        if d[1].status:
            continue

        # 주문타입 (매수 = '0' / 매도 = '1')
        # 주문정보 전송
        self.kiwoom.send_order("send_order_req", "0101", account, d[1].order_type, d[1].item_code, d[1].count, d[1].price, d[1].hoga, "")
        # 주문한 컬럼 업데이트
        cursor.execute("update order_list set status='1' where  order_date = '" + d[
            1].order_date + "' and order_type = '" + str(d[1].order_type) + "' and item_code = '" + d[
                           1].item_code + "'")

#출력하는 함수?

---