(Python) sqlite3 사용

python에서 sqlite3 사용하는 법

1. sqlite 모듈 import

import sqlite3

from sqlite3 import Error

2. db connection

def create_connection(db_file):

    try:

        conn = sqlite3.connect(db_file)

        return conn

    except Error as e:

        print(e)

 

    return None

3. select query 사용

3-1) 조건 없는 select

def select_all_tasks(conn):

    cur = conn.cursor()

    cur.execute("SELECT * FROM tasks")

 

    rows = cur.fetchall()

 

    for row in rows:

        print(row)

3-2) 조건 있는 select

def select_task_by_priority(conn, priority):

    """

    Query tasks by priority

    :param conn: the Connection object

    :param priority:

    :return:

    """

    cur = conn.cursor()

    cur.execute("SELECT * FROM tasks WHERE priority=?", (priority,))

 

    rows = cur.fetchall()

 

    for row in rows:

        print(row)

4. insert query 사용

def insert_task(conn, task):

    sql = ''' INSERT INTO tasks(name,priority,status_id,project_id,begin_date,end_date)

              VALUES(?,?,?,?,?,?) '''

    cur = conn.cursor()

    cur.execute(sql, task)

    return cur.lastrowid

※ 출처

- http://www.sqlitetutorial.net/sqlite-python/insert/

- http://www.sqlitetutorial.net/sqlite-python/sqlite-python-select/