CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT
)

import sqlite3

# 如果找不到資料庫檔，就會新建一個
con = sqlite3.connect("sample.db")
cur = con.cursor()

print('檢查 categories 資料表是否存在於 sambple.db 資料庫中:')
listOfTables = cur.execute(
    """SELECT tbl_name FROM sqlite_master WHERE type='table'
    AND tbl_name ='categories'; """).fetchall()

if listOfTables == []:
    print('categories 資料表不存在!')
else:
    print('找到 categories 資料表!')
    print("刪除舊有 categories 資料表")
    cur.execute("DROP TABLE categories")

create_sql = '''
    CREATE TABLE categories (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT
    )

'''
print("建立 categories 資料表")
cur.execute(create_sql)

print("新增資料")
cur.execute(
    """INSERT INTO categories (name, description) VALUES ("Python","python")""")


name = "PHP"
description = "php"
cur.execute("""INSERT INTO categories (name, description)
              VALUES (?, ?)
              """, (name, description))

print("列出所有資料")
statement = "SELECT * FROM categories"
cur.execute(statement)
output = cur.fetchall()
for row in output:
    print(row)
    
print("列出 id 為 1 的資料")
statement = "SELECT * FROM categories WHERE id = ?"
# 參數只有一個時，記得加上逗號，否則會有錯誤
cur.execute(statement, (id,))
result = cur.fetchone()
if result:
    print(result)


con.commit()
con.close()