python處理SQLite資料庫的方法

2022-02-21 13:01:54

前言：

資料庫非常重要，程式的資料增刪改查需要資料庫支援。python處理資料庫非常簡單。而且不同型別的資料庫處理邏輯方式大同小異。本文以sqlite資料庫為例，介紹一下python運算元據庫的方法。

一、安裝

pip3 install pysqlite3

三、資料庫連線、關閉等

import sqlite3
# 連線資料庫(如果不存在則建立)
conn = sqlite3.connect('test.db')
print("Opened database successfully")
 
# 建立遊標
cursor = conn.cursor()

# 關閉遊標
cursor.close()
# 提交事物
conn.commit()
# 關閉連線
conn.close()

四、表操作

1、建立資料表

import sqlite3

conn = sqlite3.connect('test.db')
print ("資料庫開啟成功")
c = conn.cursor()
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 ("資料表建立成功")
conn.commit()
conn.close()

2、顯示資料表數目

sql="select name from sqlite_master where type='table' order by name"
tables=cursor.execute(sql).fetchall()
print(len(tables))

3、刪除資料表

sql="DROP TABLE database_name.table_name"
cursor.execute(sql)

五、Cusor的一些方法

fetchone()

獲取查詢結果集的下一行

fetchmany(size=cursor.arraysize)

獲取查詢結果的下一組行，返回一個列表。

fetchall()

取查詢結果的所有（剩餘）行，返回一個列表。請注意，遊標的 arraysize 屬性會影響此操作的效能。當沒有行可用時返回一個空列表。

注：fetchall()用來統計表記錄時，在開頭用一次，再用則查詢為空

.description顯示欄位資訊，返回列表。

六、SQL操作

1、查

...
# 建立遊標
cursor = conn.cursor()
 
# 查詢資料
sql = "select * from Student"
values = cursor.execute(sql)
for i in values:
    print(i)
 
# 查詢資料 2
sql = "select * from Student where id=?"
values = cursor.execute(sql, (1,))
for i in values:
    print('id:', i[0])
    print('name:', i[1])
    print('age:', i[2])
 
# 提交事物
conn.commit()
...

2、增

...
# 建立遊標
cursor = conn.cursor()
 
# 插入資料
sql = "INSERT INTO Student(Name, Age) VALUES('love', 22)"
cursor.execute(sql)
 
# 插入資料 2
data = ('love2', 2221) # or ['love2', 2221]
sql = "INSERT INTO Student(Name, Age) VALUES(?, ?)"
cursor.execute(sql, data)
 
# 提交事物
conn.commit()
...

3、刪

sql語句換一下即可，看下一節的SQL語句。

4、改

sql語句換一下即可，看下一節的SQL語句。

七、SQL常用語句

# 增   兩種方法
sql1="INSERT INTO table_name VALUES (value1,value2,value3,...);"
sql2='''INSERT INTO table_name 
    (column1,column2,column3,...) 
    VALUES (value1,value2,value3,...);'''
# 刪
sql="DELETE FROM table_name WHERE [condition];"
# 改
sql="UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];"
# 查
sql="SELECT * FROM table_name;"

# 重新命名
sql="ALTER TABLE 老表名 RENAME TO 新表名;"
# 新增欄位
sql="ALTER TABLE 表名 ADD COLUMN 新列 TEXT;"

# 檢視所有欄位名
sql="PRAGMA table_info([表名])"

八、row_factory 高階操作

這是對row_factory的官方解釋(官方解釋直接忽略就好，看我下面的解釋）：A Row instance serves as a highly optimized row_factory for Connection objects. It tries to mimic a tuple in most of its features.
It supports mapping access by column name and index, iteration, representation, equality testing and len().
If two Row objects have exactly the same columns and their members are equal, they compare equal.

基礎Cursor物件只能通過數位索引來獲取值，但是我想通過列名來獲取值是做不到的。雖然可以使用Cursor.description來獲取欄位名稱，但是自己做對映寫程式碼很麻煩。
本著簡約程式碼（懶）的原則，python推出了Cursor.Row物件。其實就是列名和值做了個對映，可以通過字元索引來獲取值。很方便。
升級過程也簡單，就加一句話：conn.row_factory = sqlite3.Row

看例子：

1、使用

import sqlite3 
conn=connectSqlite("myDB.db")
conn.row_factory = sqlite3.Row # 這句一定要加
cursor=conn.cursor()
rows=cursor.execute("SELECT * from myTable ")

row=rows.fetchone()
row.key() # 返回所有欄位名，
print(row[0]) # 輸出第一個欄位
print(row["field1"]) # 輸出欄位名為field1的值

2、迴圈輸出所有值

# ...上接上面的第一塊內容
s=0
for row in rows:# 迭代就不用fetchone()了
    s+=1
    print("列印第{}個資料".format(s))
    for r in row:
        print(r)

九、範例

1、從sqlite資料庫中返回json格式資料

'''
description: 根據輸入條件，從sqlite資料庫中返回JSON資料 
param {*} db_name:str 資料庫名稱
param {*} fields:list 篩選的欄位
param {*} table_name:str 要查詢的表名
param {*} condition:str 查詢的條件，注意條件的值是字串的話需要跳脫
return {*} json
author: https://blog.csdn.net/Crayonxin2000
'''
def returnJsonFromSqlite(db_name,fields,table_name,condition):
    data = {}
    rows = []  # 資料
    conn = connectSqlite(db_name)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    fields_str = ",".join(fields)  # 列表轉欄位，fields為所有的欄位
    if condition=="" or condition==None:
        sql="SELECT {} from {} ".format(fields_str,table_name)
    else:
        sql="SELECT {} from {} WHERE {}".format(fields_str,table_name,condition)
    print(sql)
    result=cursor.execute(sql)
    for item in result:
        row = []
        for field in fields:
            row.append(item[field])
        rows.append(row)
    data["header"] = fields
    data["rows"] = rows
    # 關閉資料庫
    cursor.close()
    conn.close()
    return jsonify(data)

注：data是字典，我用flask的jsonify工具JSON化了，你可以使用其他工具。

到此這篇關於python處理SQLite資料庫的方法的文章就介紹到這了,更多相關python處理SQLite資料庫內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！