파이썬 SQLite Database 기본 사용법 정리

파이썬 SQLite Database 기본 사용법 정리

 

SQLite 를 사용함으로써 얻는 이점

• SQLite 는 큰 데이터베이스에서 요구하는 또다른 분리된 서버가 필요하지 않습니다.
• SQLite 는 설치와 administration 을 요구하지 않습니다. 즉, 바로 쉽게 사용하기 좋습니다.
• 오로지 하나의 데이터베이스 파일이 필요합니다, 그리고 모든 정보는 오로지 이 하나의 파일에서 오게됩니다. 즉 여러 파일들로 쪼개진 여타 다른 데이터베이스보다 편리합니다.
• 미리 설치된 파이썬 기본 라이브러리이므로 또다른 설치가 필요하지 않습니다.
• SQLite는 ANSI-C로 작성되었기때문에, 속도가 빠릅니다. 또한 파이썬에서 api가 사용하기 쉽도록 되어있습니다.
• SQLite는 UNIX 시스템과 윈도우에서 잘 작동됩니다.

 

데이터베이스로 연결하기

첫번째로 SQLite와 해야할 것은 데이터베이스로 연결해야하는 것입니다. sqlite3모듈의 connect() 메소드로 가능합니다.  

# importing the required modules
import sqlite3

# setting up the connection with database
conn = sqlite3.connect("sample.db")
print("Successfully connected to the database")
# closing the connection
conn.close()

이 코드는 "sample.db"라는 파일이름은 coonect()라는 함수로 연결을 합니다. connect()라는 함수는 인자값으로 데이터베이스 파일을 받고 있습니다. 만약 주어진 경로에 그 파일이 없다면 그 파일 이름에 맞는 새로운 파일을 생성하게 됩니다.  마지막으로 close()라는 함수로 데이터베이스와의 연결을 끊어줍니다.

 

SQLite3 cursor

cursor 라는 오브젝트는 데이터베이스의 인터페이스이며 SQL의 query에서 동작합니다. sqlite3를 이용해서 SQL script를 동작하기 위해서는 무조건 cursor 오브젝트를 생성해야합니다. cursor 오브젝트를 만들려면, cursor() 메소드를 이용해야합니다. 

# importing the required modules
import sqlite3
# setting up the connection to the database
conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.close()
# closing the connection
conn.close()

 

SQLite 데이터 타입

NULL: 아무것도 들어있지 않습니다.

INTEGER: 숫자형 값이며 다른 integers 개념과 비슷합니다.

REAL: decimal 소수형 값입니다.

TEXT: 텍스트 스트링입니다.

BLOB: 파일이나 이미지파일을 저장하는 바이너리 데이터입니다.

 

SQLite 데이터타입과 파이썬 데이터타입과의 비교

Python type	SQLite type
None	NULL
ing	INTEGER
float	REAL
str	TEXT
bytes	BLOB

 

SQLite 를 이용해서 테이블 만들기

SQLite를 이용해서 테이블을 만들려면 cursor오브젝트의 execute() 메소드안에서 sql의 CREATE TABLE statement를 이용해야합니다. 

CREATE TABLE table_name(
    column_name Data_type constraint,
    ...
    ...
    ...
    column_name Data_type constraint
);

위의 SQLite statement를 파이썬에서 이용하려면 아래와같은 예제로 사용하면 됩니다. 아래의 예제는 employee라는 테이블을 만드는 예제입니다.

import sqlite3

conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
table = cur.execute(""" CREATE TABLE employee(
    id INT PRIMARY KEY,
    name CHAR(25),
    salary CHAR(25),
    joining_date DATE
);
""")
print("\n [+] The table has been created Successfully ")
cur.close()
conn.close()

위의 예제에서는  employee라는 id, mame, salary, joining_date 어트리뷰트를 담고 있는 테이블을 만들었습니다. 이 테이블은 데이터를 저장하거나 쿼리를 할 수있습니다.

 

테이블에 데이터 입력하기

SQLite에서 테이블을 만들었습니다. 이제 SQL을 이용해서 데이터를 넣어보겠습니다. 기본적인 INSERT statement는 다음과 같습니다.

INSERT INTO table_name (columns_name_1, columns_name_2,...) VALUES (columns_data_1, columns_data_1,...)

위의 statement에서 table_name 은 테이블의 이름이고, colu,ms_name1, column_name2 .. 은 테이블의 column의 이름, column_data_1, column_data_2 는 column에 입력할 데이터입니다.

 

 

이제 파이썬에서 위와같은 statement를 이용해서 데이터를 넣는 예제입니다.

import sqlite3

conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.execute("INSERT INTO employee (id, name, salary, joining_date) VALUES (1001, 'David', 50000, '1-08-2019')")
cur.execute("INSERT INTO employee (id, name, salary, joining_date) VALUES (1002, 'Sam', 80000, '3-09-2020')")
cur.execute("INSERT INTO employee (id, name, salary, joining_date) VALUES (1003, 'Roshan', 90000, '8-08-2020')")
cur.execute("INSERT INTO employee (id, name, salary, joining_date) VALUES (1004, 'Kishan', 100000, '9-09-2020')")
cur.execute("INSERT INTO employee (id, name, salary, joining_date) VALUES (1005, 'Ankit', 111000, '10-05-2019')")
print("\n [+] The Data has been inserted Successfully ")
cur.close()
conn.commit()
conn.close(

execute()로 실행한뒤 cursor을 close()메소드로 닫은다음, commit() 메소드를 사용해야 데이터베이스가 close() 되기전 변경된 부분이 저장이 됩니다.

 

 테이블을 Query하기

이제 지금까지 만들었던 테이블을 쿼리를 해볼 차례입니다. 쿼리를 하기위해서는 execute() 메소드에서 SELECT statement를 사용해야합니다. 기본적인 문법은 아래와 같습니다.

SELECT columns_names FROM table_name

column_names 는 쿼리를 적용시킬 column의 이름입니다. 이제 아래의 코드에서 어떻게 employee table에 쿼리를 적용할지 알아봅시다.

import sqlite3
conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.execute("SELECT id,name FROM employee")
table = cur.fetchall()
for i in table:
print(i)

cur.close()
conn.commit()
conn.close()

 

위의 코드는 employee 테이블의 column중 id와 name을 쿼리로 빼낸 예제로, cursor 오브젝트의 fetchall() 메소드를 이용해서 데이터를 리턴 받을 수있습니다. 리턴 된 데이터는 쿼리를 했던 row형의 리스트입니다. 각각 하나씩 볼려면 for loop을 이용해서 볼 수 있습니다.

 

 

테이블에서 모든 데이터 가져오기

때때로 데이터베이스의 테이블데이터를 모두 가져와야할 때가 있습니다. 아래의 statement 예제를 보겠습니다.

SELECT * FROM table_name

* 심볼은 모든 column을 나타냅니다. 아래의 파이썬 예제로 모든 정보를 가져오는 것을 확인해보겠습니다.

import sqlite3
conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.execute("SELECT * FROM employee")
rows = cur.fetchall()
print("\n [+] Querying the data \n")
for i in rows:
print(i)

cur.close()
conn.commit()
conn.close()

 

순서정렬로 쿼리 해보기

 

때때로 Ascending, Descending 같은 정렬이 된 데이터를 가져와야할 때가 있습니다. ORDER BY 라는 statement를 사용합니다.

SELECT columns_name FROM table_name ORDER BY columns_name

import sqlite3

conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
cur.execute("SELECT * FROM employee ORDER BY name")
table = cur.fetchall()
for i in table:
print(i)

cur.close()
conn.commit()
conn.close()

다음과 같이 name column에 따라 정렬된 데이터를 받아올 수 있습니다.

 

테이블 업데이트 하기

데이터베이스에 업데이트를 해야하는 경우가 많이 생기는데, 이때는 UPDATE라는 statement를 이용합니다. 그리고 WHERE 라는 statememt를 이용해서 조건문형식으로 어느 데이터를 업데이트할 지를 정합니다.

UPDATE table_name SET update_required WHERE Some_condition

import sqlite3

conn = sqlite3.connect("sample.db")
print("\n [+] Successfully connected to the database")
cur = conn.cursor()
print("\n [+] Cursor has been set up successfully")
print("\n [+] Data Before Update\n")
cur.execute("SELECT * FROM employee")
before = cur.fetchall()
for i in before:
print(i)

cur.execute("UPDATE employee SET name = 'Aditya' where name = 'Sam'")
print("\n [+] Data After Update\n")
cur.execute("SELECT * FROM employee")
after = cur.fetchall()
for i in after:
print(i)

cur.close()
conn.commit()
conn.close()

위의 코드는 name 이 Sam일경우 Aditya로 바꾼다는 내용입니다.

 

테이블 리스트 가져오기

현재 데이터베이스에 있는 모든 테이블의 리스트를 가져오는 방법입니다.

con = sqlite3.connect("data.db")
cursor = con.cursor()

cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())