sqlite integrated
1.0.0
Paket ini menyediakan kelas untuk membuat penanganan database SQLITE3 lebih mudah. Saya telah berusaha untuk membuatnya sesederhana mungkin, dan pesan kesalahan sebanyak mungkin. Kelas Database utama menangani bacaan dan menulis ke database. Kelas DatabaseEntry mewakili entri database tunggal. Ini dapat digunakan seperti kamus untuk menetapkan nilai baru ke entri. Contoh: entry['name'] = "New Name" . Kelas Query dapat digunakan untuk membuat kueri SQL dengan atau tanpa database terlampir untuk menjalankannya.
Pasang dengan pip
pip install sqlite-integratedDokumentasi dapat ditemukan di sini.
Jika Anda tertarik dengan kode sumber terbuka, klik di sini.
Lihat perubahan dan fitur terbaru dari versi 0.0.6 di sini.
Mulailah dengan mengimpor kelas dan membuat database baru kami (ingat untuk memasukkan jalur yang valid ke file database).
from sqlite_integrated import *
db = Database ( "path/to/database.db" , new = True ) Kami lulus new=True untuk membuat file database baru.
Kami sekarang dapat membuat tabel dengan SQL. Perhatikan bahwa kami membuat kolom yang ditetapkan sebagai "kunci utama" dengan bendera primary_key . Setiap tabel harus memiliki salah satu kolom ini (agar paket ini berfungsi dengan baik). Itu memastikan bahwa setiap entri memiliki ID unik, sehingga kami dapat melacaknya.
db . create_table ( "people" , [
Column ( "person_id" , "integer" , primary_key = True ),
Column ( "first_name" , "text" ),
Column ( "last_name" , "text" )
]) Kita dapat melihat ikhtisar tabel dalam database dan bidang tabelnya dengan overview metode.
db . overview ()Output :
Tables
people
person_id
first_name
last_name
Untuk menambahkan entri, gunakan metode add_entry .
db . add_entry ({ "first_name" : "John" , "last_name" : "Smith" }, "people" )Mari kita tambahkan beberapa lagi!
db . add_entry ({ "first_name" : "Tom" , "last_name" : "Builder" }, "people" )
db . add_entry ({ "first_name" : "Eva" , "last_name" : "Larson" }, "people" ) Untuk melihat ke database, kita dapat menggunakan metode table_overview .
db . table_overview ( "people" )Keluaran:
person_id ║ first_name ║ last_name
══════════╬════════════╬═══════════
1 ║ John ║ Smith
2 ║ Tom ║ Builder
3 ║ Eva ║ Larson
Simpan perubahan Anda dengan save :
db . save ()Mulailah dengan mengimpor kelas dan membuka database kami.
from sqlite_integrated import Database
db = Database ( "tests/test.db" )Hanya untuk memeriksa Anda sekarang dapat menjalankan.
db . overview ()Ini akan mencetak daftar semua tabel dalam database.
Simpan dan tutup database dengan close :
db . close ()Kami mulai dengan mendapatkan entri. Dalam hal ini entri ke -3 dalam tabel "Pelanggan".
entry = db . get_entry_by_id ( "customers" , 3 )Sekarang edit sebanyak yang Anda inginkan!
entry [ "FirstName" ] = "John"
entry [ "LastName" ] = "Newname"
entry [ "City" ] = "Atlantis" Untuk memperbarui tabel kami, kami cukup menggunakan metode update_entry .
db . update_entry ( entry ) Untuk menyimpan perubahan ini ke file database, gunakan metode 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" ])Keluaran:
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
db = Database . in_memory () # 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 . in_memory ()
# 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 )Keluaran:
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" , verbose = True )
# 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: { list ( query . run ()) } " )Keluaran:
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'})]
Kita dapat melihat bahwa hanya ada dua pelanggan dengan nama depan yang dimulai dengan 'T'.
Secara default database mencetak SQL yang dieksekusi dalam database, ke terminal. Ini dapat dinonaktifkan dengan lewat silent=True dengan metode run .
from sqlite_integrated import Database
# Loading an existing database
db = Database ( "tests/test.db" , verbose = True )
# Metadata for the entry we are adding
entry = { "FirstName" : "Test" , "LastName" : "Testing" , "Email" : "[email protected]" }
# Adding the entry to the table called "customers"
db . INSERT_INTO ( "customers" ). VALUES ( entry ). run ()
# A little space
print ()
# Print the table
db . table_overview ( "customers" , get_only = [ "CustomerId" , "FirstName" , "LastName" , "Email" , "City" ], max_len = 10 )Keluaran:
Executed sql: INSERT INTO customers (FirstName, LastName, Email) VALUES ('Test', 'Testing', '[email protected]')
CustomerId ║ FirstName ║ LastName ║ Email ║ City
═══════════╬═══════════╬══════════════╬═══════════════════════════════╬═══════════════════
1 ║ Luís ║ Gonçalves ║ [email protected] ║ São José dos Campos
2 ║ Leonie ║ Köhler ║ [email protected] ║ Stuttgart
3 ║ François ║ Tremblay ║ [email protected] ║ Montréal
4 ║ Bjørn ║ Hansen ║ [email protected] ║ Oslo
5 ║ František ║ Wichterlová ║ [email protected] ║ Prague
.
.
.
56 ║ Diego ║ Gutiérrez ║ [email protected] ║ Buenos Aires
57 ║ Luis ║ Rojas ║ [email protected] ║ Santiago
58 ║ Manoj ║ Pareek ║ [email protected] ║ Delhi
59 ║ Puja ║ Srivastava ║ [email protected] ║ Bangalore
60 ║ Test ║ Testing ║ [email protected] ║ 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 )Keluaran:
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 . in_memory ()
# 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 )Keluaran:
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" , verbose = True )
# Loading the same database to a different variable
db2 = Database ( "tests/test.db" , verbose = True )
# 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 = list ( query . run ( db1 ))
out2 = list ( query . run ( db2 ))
# Printing the outputs
print ( f" n db1 output: n { out1 } " )
print ( f" n db2 output: n { out2 } " )Keluaran:
Executed sql: UPDATE customers SET FirstName = 'Allan', LastName = 'Changed' WHERE CustomerId = 1
Executed sql: SELECT * FROM customers WHERE CustomerId = 1
Executed sql: SELECT * FROM customers WHERE CustomerId = 1
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': '[email protected]', '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': '[email protected]', 'SupportRepId': 3})]
Saya akan lebih dari senang jika ada yang menemukan ini cukup berguna untuk ditambahkan, atau memodifikasi kode ini.
