설치
[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]
