这是什么？

这个包提供了一些类来简化 sqlite3 数据库的处理。我努力使它尽可能简单，并且错误消息尽可能有用。主Database类处理对数据库的读取和写入。该类DatabaseEntry表示单个数据库条目。它可以像字典一样用于为条目分配新值。例如：entry['name'] = "New Name"。该类Query可用于创建带有或不带有附加数据库的 sql 查询来运行它。

安装

使用 pip 安装

pip install sqlite-integrated

阅读文档

文档可以在这里找到。

Github 回购

如果您对开源代码感兴趣，请单击此处。

如何使用它！

创建新数据库

首先导入该类并创建我们的新数据库（请记住放入数据库文件的有效路径）。

from sqlite_integrated import *
db = Database("path/to/database.db", new=True)

我们通过new=True创建一个新的数据库文件。

我们现在可以用 sql 创建一个表。请注意，我们使用primary_key标志创建了一个指定为“PRIMARY KEY”的列。每个表都应该有这些列之一（这个包才能正常工作）。它确保每个条目都有一个唯一的 id，以便我们可以跟踪它。

db.create_table("people", [
    Column("id", "integer", primary_key=True),
    Column("first_name", "text"),
    Column("last_name", "text")
])

我们可以通过 方法查看数据库中表及其表字段的概览overview。

db.overview()

输出：

Tables
	people
		id
		first_name
		last_name

要添加条目，请使用该add_entry方法。

db.add_entry({"first_name": "John", "last_name": "Smith"}, "people")

让我们再添加一些！

db.add_entry({"first_name": "Tom", "last_name": "Builder"}, "people")
db.add_entry({"first_name": "Eva", "last_name": "Larson"}, "people")

要查看数据库，我们可以使用该table_overview方法。

db.table_overview("people")

输出：

id ║ first_name ║ last_name
═══╬════════════╬═══════════
1  ║ John       ║ Smith    
2  ║ Tom        ║ Builder  
3  ║ Eva        ║ Larson   

打开现有数据库

首先导入类并打开我们的数据库。

from sqlite_integrated import Database
db = Database("tests/test.db")

只是为了检查你现在可以运行。

db.overview()

这将打印数据库中所有表的列表。

编辑条目

我们从获取条目开始。在这种情况下，“客户”表中的第三个条目。

entry = db.get_entry_by_id("customers", 3)

现在随心所欲地编辑！

entry["FirstName"] = "John"
entry["LastName"] = "Newname"
entry["City"] = "Atlantis"

要更新我们的表格，我们可以简单地使用该update_entry方法。

db.update_entry(entry)

要将这些更改保存到数据库文件，请使用该save方法。

更多示例

查看表格

from sqlite_integrated import Database

# Loading an existing database
db = Database("tests/test.db")

db.table_overview("customers", max_len=15, get_only=["FirstName", "LastName", "Address", "City"])

输出：

FirstName ║ LastName     ║ Address                                  ║ City               
══════════╬══════════════╬══════════════════════════════════════════╬════════════════════
Luís      ║ Gonçalves    ║ Av. Brigadeiro Faria Lima, 2170          ║ São José dos Campos
Leonie    ║ Köhler       ║ Theodor-Heuss-Straße 34                  ║ Stuttgart          
François  ║ Tremblay     ║ 1498 rue Bélanger                        ║ Montréal           
Bjørn     ║ Hansen       ║ Ullevålsveien 14                         ║ Oslo               
František ║ Wichterlová  ║ Klanova 9/506                            ║ Prague             
Helena    ║ Holý         ║ Rilská 3174/6                            ║ Prague             
Astrid    ║ Gruber       ║ Rotenturmstraße 4, 1010 Innere Stadt     ║ Vienne             
Daan      ║ Peeters      ║ Grétrystraat 63                          ║ Brussels           
Kara      ║ Nielsen      ║ Sønder Boulevard 51                      ║ Copenhagen         
Eduardo   ║ Martins      ║ Rua Dr. Falcão Filho, 155                ║ São Paulo          
    .
    .
    .
Mark      ║ Taylor       ║ 421 Bourke Street                        ║ Sidney             
Diego     ║ Gutiérrez    ║ 307 Macacha Güemes                       ║ Buenos Aires       
Luis      ║ Rojas        ║ Calle Lira, 198                          ║ Santiago           
Manoj     ║ Pareek       ║ 12,Community Centre                      ║ Delhi              
Puja      ║ Srivastava   ║ 3,Raj Bhavan Road                        ║ Bangalore          

在内存中创建数据库

from sqlite_integrated import Database

# remember to pass new=True
db = Database(":memory:", new=True)

使用外键创建表

# importing the classes
from sqlite_integrated import Database
from sqlite_integrated import Column
from sqlite_integrated import ForeignKey

# Creating a database in memory
db = Database(":memory:", new=True)

# Creating a table of people
db.create_table("people", [
    Column("PersonId", "integer", primary_key=True),
    Column("PersonName", "text")
])

# Creating a table of groups 
db.create_table("groups", [
    Column("GroupId", "integer", primary_key=True),
    Column("GroupName", "text")
])

# A table that links people and the groups they are part off
db.create_table("person_group", [
    Column("PersonId", "integer", foreign_key=ForeignKey("people", "PersonId", on_update="CASCADE", on_delete="SET NULL"))
])

# use more=True to show more column information
db.overview(more=True)

输出：

Tables
	people
		PersonId		[Column(PersonId, integer, PRIMARY KEY)]
		PersonName		[Column(1, PersonName, text)]
	groups
		GroupId		[Column(GroupId, integer, PRIMARY KEY)]
		GroupName		[Column(1, GroupName, text)]
	person_group
		PersonId		[Column(PersonId, integer, FOREIGN KEY (PersonId) REFERENCES people (PersonId) ON UPDATE CASCADE ON DELETE SET NULL)]

使用查询

选择语句

from sqlite_integrated import Database

# Loading an existing database
db = Database("tests/test.db")

# Select statement
query = db.SELECT(["FirstName"]).FROM("customers").WHERE("FirstName").LIKE("T%")

# Printing the query
print(f"query: {query}")

# Running the query and printing the results
print(f"Results: {query.run()}")

输出：

query: > SELECT FirstName FROM customers WHERE FirstName LIKE 'T%' <
Executed sql: SELECT FirstName FROM customers WHERE FirstName LIKE 'T%' 
Results: [DatabaseEntry(table: customers, data: {'FirstName': 'Tim'}), DatabaseEntry(table: customers, data: {'FirstName': 'Terhi'})]

我们可以看到只有两个名字以“t”开头的客户。

默认情况下，数据库将在数据库中执行的 sql 打印到终端。这可以通过传递silent=True给run方法来禁用。

插入语句

from sqlite_integrated import Database

# Loading an existing database
db = Database("tests/test.db")

# Metadata for the entry we are adding
entry = {"FirstName": "Test", "LastName": "Testing", "Email": "test@testing.com"}

# Adding the entry to the table called "customers"
db.INSERT_INTO("customers").VALUES(entry).run()

# A little space
print("\n")

# Print the table 
db.table_overview("customers", get_only=["CustomerId", "FirstName", "LastName", "Email", "City"], max_len=10)

输出：

CustomerId ║ FirstName ║ LastName     ║ Email                         ║ City               
═══════════╬═══════════╬══════════════╬═══════════════════════════════╬═══════════════════
1          ║ Luís      ║ Gonçalves    ║ luisg@embraer.com.br          ║ São José dos Campos
2          ║ Leonie    ║ Köhler       ║ leonekohler@surfeu.de         ║ Stuttgart          
3          ║ François  ║ Tremblay     ║ ftremblay@gmail.com           ║ Montréal           
4          ║ Bjørn     ║ Hansen       ║ bjorn.hansen@yahoo.no         ║ Oslo               
5          ║ František ║ Wichterlová  ║ frantisekw@jetbrains.com      ║ Prague             
    .
    .
    .
56         ║ Diego     ║ Gutiérrez    ║ diego.gutierrez@yahoo.ar      ║ Buenos Aires       
57         ║ Luis      ║ Rojas        ║ luisrojas@yahoo.cl            ║ Santiago           
58         ║ Manoj     ║ Pareek       ║ manoj.pareek@rediff.com       ║ Delhi              
59         ║ Puja      ║ Srivastava   ║ puja_srivastava@yahoo.in      ║ Bangalore          
60         ║ Test      ║ Testing      ║ test@testing.com              ║ None               

更新声明

from sqlite_integrated import Database

# Loading an existing database
db = Database("tests/test.db")

# Printing an overview of the customers table
db.table_overview("customers", get_only=["CustomerId", "FirstName", "LastName", "City"], max_len=10)

# Some space
print()

# Update all customers with a first name that starts with 'L', so that all their names are now Brian Brianson. 
db.UPDATE("customers").SET({"FirstName": "Brian", "LastName": "Brianson"}).WHERE("FirstName").LIKE("L%").run()

# Some more space
print()

# Printing an overview of the updated customers table
db.table_overview("customers", get_only=["CustomerId", "FirstName", "LastName", "City"], max_len=10)

输出：

CustomerId ║ FirstName ║ LastName     ║ City               
═══════════╬═══════════╬══════════════╬════════════════════
1          ║ Luís      ║ Gonçalves    ║ São José dos Campos
2          ║ Leonie    ║ Köhler       ║ Stuttgart          
3          ║ François  ║ Tremblay     ║ Montréal           
4          ║ Bjørn     ║ Hansen       ║ Oslo               
5          ║ František ║ Wichterlová  ║ Prague             
    .
    .
    .
55         ║ Mark      ║ Taylor       ║ Sidney             
56         ║ Diego     ║ Gutiérrez    ║ Buenos Aires       
57         ║ Luis      ║ Rojas        ║ Santiago           
58         ║ Manoj     ║ Pareek       ║ Delhi              
59         ║ Puja      ║ Srivastava   ║ Bangalore          



CustomerId ║ FirstName ║ LastName     ║ City               
═══════════╬═══════════╬══════════════╬════════════════════
1          ║ Brian     ║ Brianson     ║ São José dos Campos
2          ║ Brian     ║ Brianson     ║ Stuttgart          
3          ║ François  ║ Tremblay     ║ Montréal           
4          ║ Bjørn     ║ Hansen       ║ Oslo               
5          ║ František ║ Wichterlová  ║ Prague             
    .
    .
    .
55         ║ Mark      ║ Taylor       ║ Sidney             
56         ║ Diego     ║ Gutiérrez    ║ Buenos Aires       
57         ║ Brian     ║ Brianson     ║ Santiago           
58         ║ Manoj     ║ Pareek       ║ Delhi              
59         ║ Puja      ║ Srivastava   ║ Bangalore          

删除查询

from sqlite_integrated import Database
from sqlite_integrated import Query
from sqlite_integrated import Column

# Creating a database in memory
db = Database(":memory:", new=True)

# Adding a table of people
db.create_table("people", [
    Column("id", "integer", primary_key=True),
    Column("name", "text")
])

# Adding a few people
db.add_entry({"name": "Peter"}, "people")
db.add_entry({"name": "Anna"}, "people")
db.add_entry({"name": "Tom"}, "people")
db.add_entry({"name": "Mads"}, "people")
db.add_entry({"name": "Simon"}, "people")
db.add_entry({"name": "Emillie"}, "people")
db.add_entry({"name": "Mathias"}, "people")
db.add_entry({"name": "Jakob"}, "people")

# ids of entries to delete
ids = [1,2,5,7]

print("Before deletion:")
db.table_overview("people", max_len=10)

# Deletes the ids from the 'people' table
for c_id in ids:
    db.DELETE_FROM("people").WHERE("id", c_id).run()

print("After deletion:")
db.table_overview("people", max_len=10)

输出：

Before deletion:
id ║ name   
═══╬══════════
1  ║ Peter  
2  ║ Anna   
3  ║ Tom    
4  ║ Mads   
5  ║ Simon  
6  ║ Emillie
7  ║ Mathias
8  ║ Jakob  

After deletion:
id ║ name   
═══╬══════════
3  ║ Tom    
4  ║ Mads   
6  ║ Emillie
8  ║ Jakob  

未附加的查询

from sqlite_integrated import Database
from sqlite_integrated import Query

# Loading an existing database
db1 = Database("tests/test.db")

# Loading the same database to a different variable
db2 = Database("tests/test.db")

# Updating the first entry in the first database only
db1.UPDATE("customers").SET({"FirstName": "Allan", "LastName": "Changed"}).WHERE("CustomerId", 1).run()

# This query gets the first entry in the customers table
query = Query().SELECT().FROM("customers").WHERE("CustomerId = 1")

# Running the query on each database and printing the output.
out1 = query.run(db1)
out2 = query.run(db2)

# Printing the outputs
print(f"\ndb1 output: {out1}")
print(f"\ndb2 output: {out2}")

输出：

db1 output: [DatabaseEntry(table: customers, data: {'CustomerId': 1, 'FirstName': 'Allan', 'LastName': 'Changed', 'Company': 'Embraer - Empresa Brasileira de Aeronáutica S.A.', 'Address': 'Av. Brigadeiro Faria Lima, 2170', 'City': 'São José dos Campos', 'State': 'SP', 'Country': 'Brazil', 'PostalCode': '12227-000', 'Phone': '+55 (12) 3923-5555', 'Fax': '+55 (12) 3923-5566', 'Email': 'luisg@embraer.com.br', 'SupportRepId': 3})]

db2 output: [DatabaseEntry(table: customers, data: {'CustomerId': 1, 'FirstName': 'Luís', 'LastName': 'Gonçalves', 'Company': 'Embraer - Empresa Brasileira de Aeronáutica S.A.', 'Address': 'Av. Brigadeiro Faria Lima, 2170', 'City': 'São José dos Campos', 'State': 'SP', 'Country': 'Brazil', 'PostalCode': '12227-000', 'Phone': '+55 (12) 3923-5555', 'Fax': '+55 (12) 3923-5566', 'Email': 'luisg@embraer.com.br', 'SupportRepId': 3})]

贡献

如果有人发现这足以添加或修改此代码，我将非常高兴。