파이썬에서 sqlite3 사용하기 > IT 기술백서

IT 기술백서

직접 알아내거나 검색하기 귀찮아서 모아 둔 것

python | 파이썬에서 sqlite3 사용하기

본문

설치

[code]

pip install pysqlite3

[/code]

 

 

[code]

import os

import sqlite3


# CONNECTION

def connectDB():

    if not os.path.isdir('./data'):

        os.makedirs("./data")

    return sqlite3.connect('./data/database.db')



# CREATE TABLE

def createTable():

    conn = connectDB()

    with conn:

        sql = """

            CREATE TABLE IF NOT EXISTS accounts(

                id INTEGER PRIMARY KEY AUTOINCREMENT, 

                user_id TEXT, 

                password TEXT,

                name TEXT,

                age TEXT

            )

        """

        cur = conn.cursor()

        cur.execute(sql)

        conn.commit()

        


# SELECT ONE

def loadOne():


    conn = connectDB()

    conn.row_factory = sqlite3.Row

    with conn:

        sql = "SELECT * FROM accounts WHERE id=:id"

        cursor = conn.cursor()

        cursor.execute(sql, {"id": 1})

        row = dict(cursor.fetchone())

        print(row["user_id"], row["name"], row["age"])


# SELECT MANY

def loadMany():


    conn = connectDB()

    conn.row_factory = sqlite3.Row

    with conn:

        sql = "SELECT * FROM accounts ORDER BY id"

        cursor = conn.cursor()

        cursor.execute(sql)

        rows = cursor.fetchall()

        

        for row in rows:

            row = dict(row)

            print(row["user_id"], row["name"], row["age"])


# INSERT

def inertData():

    conn = connectDB()

    with conn:

        cursor = conn.cursor()

        

        sql = f"""

            INSERT INTO accounts

            (user_id, password, name, age) 

            VALUES(?, ?, ?, ?)

            """

        cursor.execute(sql, (user_id, password, name, age))

        conn.commit()

        

        #가장 최근에 입력된 id값

        print(cursor.lastrowid)



# UPDATE

def updateData(id):

    conn = connectDB()

    with conn:

        cursor = conn.cursor()

        

        sql = f"""

            UPDATE accounts SET user_id=?, password=?, name=?, age=? WHERE id=?

            """

        cursor.execute(sql, (user_id, password, name, age, id))

        conn.commit()

        

# DELETE

def deleteData(id):

    conn = connectDB()

    with conn:

        cursor = conn.cursor()

        

        sql = f"DELETE FROM accounts WHERE id=?"

        cursor.execute(sql, (id))

        conn.commit() 

[/code]

댓글 0개

등록된 댓글이 없습니다.

Menu