Python连接SQLite

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