Python 連接配接 SQLite

記錄自己學習Python的代碼。

每天進步一點點，目标距離縮小點。

#! /usr/bin/python3
#-*- coding: utf-8 -*-

import sqlite3
# 在 磁盤上建立資料庫
# with sqlite3.connect('\\workspace\\PycharmProjects\\DB\\sqlite\\test1.db') as conn:
# 在記憶體中 建立資料庫
# 可以通過使用with語句來省去顯示的調用close方法關閉連接配接和遊标
with sqlite3.connect(':memory:') as conn:
    # conn 資料庫連接配接對象，并傳回資料庫連接配接
    print("Opened database successfully")
    # 建立遊标
    c = conn.cursor()
    # 執行SQL語句
    c.execute('''CREATE TABLE COMPANY
           (ID INT PRIMARY KEY     NOT NULL,
           NAME           TEXT    NOT NULL,
           AGE            INT     NOT NULL,
           ADDRESS        CHAR(50),
           SALARY         REAL);''')
    print("Table created successfully")
    # 送出事務
    # conn.commit()

    c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )")
    # sqlite3 提供兩種占位符(? 和命名占位符)
    c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (?, ?, ?, ?, ? )", (2, 'Allen', 25, 'Texas', 15000.00))
    # c.execute("INSEprint("Operation done successfully")RT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
    c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")
    c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")
    print("修改記錄行數 :", conn.total_changes)
    # conn.commit()
    print("Records created successfully")


    cursor = c.execute("SELECT id, name, address, salary  from COMPANY")
    for row in cursor:
        # print("ID = ", row[0], ",NAME = ", row[1], ",ADDRESS = ", row[2], ",SALARY = ", row[3], "\n")
        print("ID = %s,NAME = %s,ADDRESS = %s,SALARY = %d\n" % (row[0], row[1], row[2], row[3]))           

控制台輸出：

Opened database successfully
Table created successfully
修改記錄行數 : 4
Records created successfully
ID = 1,NAME = Paul,ADDRESS = California,SALARY = 20000

ID = 2,NAME = Allen,ADDRESS = Texas,SALARY = 15000

ID = 3,NAME = Teddy,ADDRESS = Norway,SALARY = 20000

ID = 4,NAME = Mark,ADDRESS = Rich-Mond ,SALARY = 65000

Process finished with exit code 0