python3 - sqlite3 example

접속/해제 (file)

import sqlite3

try:
    conn = sqlite3.connect(db_file) # 데이터베이스 연결
except sqlite3.Error as er:
    print("An error occurred:", er.args[0])
    
	conn = sqlite3.connect(db_file)

conn.close()	# 연결 종료

 

테이블 생성

#테이블 샐성
cur = conn.cursor()
try:
    cur.execute("CREATE TABLE 'main' ('index' INTEGER,'test1' TEXT,'test2' TEXT)")
except sqlite3.Error as er:
    print("An error occurred:", er.args[0])

 

입력 (INSERT)

#정보 입력
cur = conn.cursor()

index_value = 10
test1_string = "testman1"
test2_string = "testman2"
try:
    cur.execute("INSERT INTO main (start,test1,test2) Values(\'%d\',\'%s\',\'%s\')" % (index_value,test1_string,test2_string))
    conn.commit()		# commit !! -> 필수 
except sqlite3.Error as er:
    print("INSERT An error occurred:", er.args[0])

 

 

조회 (SELECT)

#단건 조회
cur = conn.cursor()
cur.execute('SELECT * FROM main')
result1 = cur.fetchone()

-> 출력 결과

(1, 'Aaa', 'bbb') // 배열로 나옴 index 0 ~ 접근

#다수 조회 -> 지정된 크기  지정 
cur = conn.cursor()
cur.execute('SELECT * FROM main')
result2 = cur.fetchmany(2)  # 2개만 읽겠다 

print(result2)

-> 결과
[(1, 'Aaa', 'bbb'), (10, 'testman1', 'testman2')]

#다수 조회
cur = conn.cursor()
cur.execute('SELECT * FROM main')
result3 = cur.fetchall()

for row_value in result3:

    print(f'{row_value[0]} = {row_value[0]} = {row_value[0]}')

---> result
[(1, 'Aaa', 'bbb'), (10, 'testman1', 'testman2')]

 

변경 (UPDATE)

#변경
update_value = "update_done"
cur = conn.cursor()
try:
    cur.execute("UPDATE main SET test1 = \'%s\' WHERE start = 10" %(update_value))
    conn.commit()

except sqlite3.Error as er:
    print("UPDATE An error occurred:", er.args[0])

print("update done ")