파이썬 Python 에서 Sqlite3 Query 사용하기

파이썬에서 주식. 선물. 옵션 데이터 수집을 위하여 Sqlite3를 사용하고 있습니다.

 

Sqlite3는 트랜잭션을 제공해주는 파일 기반의 DBMS로

라이브러리와 같이 프로그램에 직접 Embed하여 사용하는 오픈소스입니다.

 

사용하려면,

먼저 데이터베이스를 나타내는 Connection 객체를 만들어야 하고

그다음 Cursor 객체를 만들고

execute() 메서드를 호출하여 SQL 명령을 수행

commit으로 변경을 완성하고

close 하는 방식으로 동작합니다.

 

먼저 필수적으로 사용되는 객체들을 정의하고 사용예제를 작성해보려고 합니다.

 

 

파이썬에서 Sqlite3 사용하기

 

 

 Sqlite3  Connection클래스와  Cursor클래스 

 

• Connection클래스 : 연결된 데이터베이스를 동작시키는 역할
• Cursor클래스 : 실질적으로 데이터베이스에 SQL문장을 수행하고, 조회 된 결과를 가지고 오는 역할

 

객체	설명	클래스
sqlite3.connect(database[, timeout, isolation_level, detect_types, factory]	SQLite3 DB에 연결하고 연결된 Connection객체를 반환	전역함수
Connection.cursor()	Cursor객체를 생성	Connection클래스
Connection.commit()	변경내역을 DB에 반영	Connection클래스
Connection.rollback()	가장 최근의 commit()이후 지금까지 작업한 내용을 취소	Connection클래스
Connection.close()	DB연결을 종료 close()메서드를 호출하기 이전에 commit()/rollback()중 하나를 명시적으로 호출	Connection클래스
Connection.execute(sql[, parameters]) Connection.executemany(sql[, parameters]) Connection.executescript(sql_script)	임시 Cursor객체를 생성하여 해당 execute계열메서드를 수행 Cursor클래스의 해당 메서드와 동일	Connection클래스
Connection.create_aggregate, Connection.iterdump()
Cursor.execute(sql[, parameters])	SQL문장을 실행	Cursor클래스
Cursor.executemany(sql, seq_of_parameters)	동일한 SQL문장을 파라미터만 변경하며 수행	Cursor클래스
Curosr.executescript(sql_script)	세미콜론으로 구분된 연속된 SQL문장을 수행	Cursor클래스
Cursor.fetchone()	조회된 결과(Record Set)로부터 데이터 1개를 반환한다. 더 이상 데이터가 없는 경우 None을 반환	Cursor클래스
Cursor.fetchmany([size=cursor.arraysize])	조회된 결과로부터 입력받은 size만큼의 데이터를 리스트 형태로 반환한다. 데이터가 없는 경우 빈 리스트를 반환	Cursor클래스
Cursor.fetchall()	조회된 결과 모두를 리스트형태로 반환한다. 데이터가 없는 경우, 빈리스트를 반환	Cursor클래스

 

 

 Sqlite3  내장함수

 

함수	설명
abs(x)	인자의 절대값을 반환
length(x)	문자열의 길이를 반환
lower(x)	인자로 받은 문자열을 소문자로 반환 원본문자열은 변화없음
upper(x)	인자로 받은 문자열을 대문자로 반환 원본문자열은 변화없음
min(x, y, ...)	인자 중 가장 작은 값을 반환
max(x, y, ...)	인자 중 가장 큰 값을 반환
random(*)	임의의 정수를 반환
count(x)	조회 결과 중 필드 인자가 NULL이 아닌 튜플의 개수를 반환
count(*)	조회 결과의 튜플의 개수를 반환
sum(x)	조회 결과중 필드 인자희 합을 반환
예제	cur.execute("SELECT max(Age), min(Age), sum(Age) FROM 테이블") #최댓값, 최솟값, 총합

 

 

 1. Sqlite3 DBMS 연결 예제

 

import sqlite3

#...예제 1 .....
conn = sqlite3.connect("C:\study\data\DBMS\kw_option.db", isolation_level=None)
cur = conn.cursor()  # 커서 획득

#...예제 2 : 메모리 사용.....
conn = sqlite3.connect(":memory:")
cur = conn.cursor()



#...오류처리 ....
except sqlite3.Error as e:
        print("sqlite3.Erro occurred:", e.args[0])

finally:
        conn.commit()    ###---  또는 conn.rollback()
        conn.close()

 

 

 2. 테이블 생성 및 변경 예제

 

table_name = "sec_price_kospi"

sql = "CREATE TABLE IF NOT EXISTS {} (code_sector text, date_tr text, price_close INTEGER,   volume_tr INTEGER, \
               price_open INTEGER, price_high INTEGER, price_low INTEGER, price_before INTEGER,  amount_tr integer,\
               gb_sector1 text, gb_sector2 text, primary key(code_sector , date_tr))".format(table_name)

cur.execute(sql)

##--- 위에서 IF NOT EXISTS 는 테이블이 없으면 생성하라는 의미임

##--- 기존의 테이블 속성과 데이터 복사해서 생성하는 SQL 문 예제
CREATE TABLE new_table AS SELECT column1, column2, column3 FROM old_table;

##--- 테이블명을 변경하는 SQL 예제
ALTER TABLE old_table RENAME TO new_table;

저는 여기서 동일한 구조의 다른 테이블을 반복해서 생성하기 위하여 sql문에  파이썬의 format함수를 사용했습니다.

 

 

 

<Tip : format 함수 사용법 >

# format 함수 사용법

# '{인덱스0}, {인덱스1}'.format(값0, 값1)

# ==> 값0 은 {인덱스0} 으로 값1 은 {인덱스1} 로 대체되어 문자열이 만들어짐


#----인덱스로 대입하는  예제 입니다 ----
a = 3
b = 4
 
s1 = '구구단 {0} x {1} = {2}'.format(a, b, a * b)
print(s1)


# ----이름으로 대입하는 예제입니다  ----

s2 = 'number : {num}, gender : {gen}'.format(num=100, gen='남')
print(s2)

 

 3. 데이터 입력 예제

 

..........
##.....예제 1.....

df_to_list = df.values.tolist()  ### 데이터프레임을 리스트로 변환

sql_insert = f"INSERT OR IGNORE INTO  {table_name} (code_option, date_tr, price_close,  volume_tr, \
                         price_open,  price_high, price_low, price_before, yyyymm_act ,gb_option )  \
                         VALUES(?,?,?,?,?,?,?,?,?,?)"
cur.executemany(sql_insert, (df_to_list))


##.....예제 2.  튜플 데이터....

s_tuple = (
    (1,  '2023-00-00'),
    (2,  '2023--00-00'),
    (3,  '2023--00-00')
)

cur.executemany("INSERT INTO table1(id, date_tr) VALUES(?,?)", s_tuple)

##.....예제 3.  1개 데이터...

cur.execute("INSERT INTO table1(id, date_tr) VALUES(?,?)", (4,  '2023--00-00'))

 

<Tip 2  : f스트링 사용법 >

파이썬에서 모든 표현식을 문자열 안에 삽입

>>> str1 = "Python"
>>> f"{str1}의 앞 두 글자는 {str1[:2]}입니다."
'Python의앞 두 글자는  Py입니다.'

>>> f"{str1}를 거꾸로 하면 {str1[::-1]}입니다."
'Python를 거꾸로 하면 nohtyP입니다.'

>>> f"2회 반복: {','.join([str1] * 2)}"
'2회 반복: Python,Python'

 

 

 4. 데이터 조회 예제

 

##---- 예제 1

sql_select = f"SELECT code_option FROM code_option where yymm_act = '{yyyymm}'"
cur.execute( sql_select )
code_list = cur.fetchall()

##---- 예제 2  
for row in cur.fetchall():
    print(row)
    
# ---- 예3  
for row in cur.execute("SELECT * FROM table1 ORDER BY id ASC"):
    print(row)

 

 5. 데이터 수정 예제

 

###---  예제 1
c.execute("UPDATE code_table SET name=? WHERE id=?", ('code1', 1))

###---  예제 2
c.execute("UPDATE code_table SET name='%s' WHERE id='%s'" ('code3', 5))   # %s %d %f

###---  예제 3
c.execute("UPDATE code_table SET name=:name WHERE id=:id", {"name": 'code2', 'id': 3})

 

 

 6. 데이터 삭제 예제

 

###---  예제 1
c.execute("DELETE FROM code_table WHERE id=?", (1, ))

###---  예제 2

c.execute("DELETE FROM code_table WHERE id=:id", {'id': 3})

###---  예제 3  --- 뒤에 rowcount를 붙여주면 지운 행 개수를 돌려준다.

print(conn.execute("DELETE FROM code_table").rowcount)

 

 

 7. 데이터 백업 예제

 

with conn:
    with open('dump.sql', 'w') as f:
        for line in conn.iterdump():
            f.write('%s\n' % line)
        print('Completed.')

 

---

https://datawith.tistory.com/82

SQLite 사용법 (Create, Alter, Insert, Update, Select, Delete.. )

---

아래 사이트는 Sqlite 튜토리얼 사이트 입니다. 더 깊은 내용을 공부하고 싶은 분들에게 도움이 될 것 같습니다.

 

https://www.sqlitetutorial.net/sqlite-union/

SQLite UNION with Examples, UNION vs. UNION ALL

 

---