Do you want to add the full power, functionality, and flexibility of SQL to your Python Windows GUI Builder, without installing external libraries? You can build it easily by combining SQLite-a built-in DB-API 2.0 interface for SQLite databases in Python and Python4Delphi library, inside Delphi and C++Builder.
SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language.
Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.
The sqlite3 module was written by Gerhard Häring. It provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249.
Table of Contents
Why should I use SQLite in my apps?
The following are highlights of the distinctive features Of SQLite:
- Zero-Configuration: SQLite does not need to be “installed” before it is used.
- Serverless: With SQLite, the process that interacts with the database reads and writes directly from the database files on disk. There is no intermediary server process.
- Single Database File: Single ordinary disk file and can be located anywhere in the directory hierarchy. If SQLite can read the disk file then it can read anything in the database. If the disk file and its directory are writable, then SQLite can change anything in the database. Database files can easily be copied or shared.
- Stable Cross-Platform Database File: file format is cross-platform. A database file written on one machine can be copied to and used on a different machine with a different architecture.
- Compact: The whole SQLite library with everything enabled is less than 500kb in size. Unneeded features can be disabled at compile-time to further reduce the size of the library to under 300kb if desired.
- Manifest Typing: The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use, especially when used in combination with dynamically typed programming languages such as Tcl and Python.
- Variable-Length Records: Use only the amount of disk space needed to store the information in a row. It results in smaller database files, makes the database run faster, since there is less information to move to and from disk.
- Readable Source Code: Designed to be readable and accessible to the average programmer.
- SQL Statements Compile into Virtual Machine Code: The virtual machine provides a crisp, well-defined junction between the front-end of SQLite (the part that parses SQL statements and generates virtual machine code) and the back-end (the part that executes the virtual machine code and computes a result).
- Public Domain: No claim of copyright is made on any part of the core source code.
- SQL Language Extensions: Provides a number of new syntax and enhancements to the usual SQL language that are not normally found in other database engines.
Is there an example of using SQLite in a Python GUI app?
This post will guide you on creating a simple database, insert data, and run a simple query using SQLite in Python GUI for Delphi Windows App.
Prerequisites: Before we begin to work, download and install the latest Python for your platform. Follow the Python4Delphi installation instructions mentioned here. Alternatively, you can check out the easy instructions found in this video Getting started with Python4Delphi.
First, open and run our Python GUI using project Demo1 from Python4Delphi with RAD Studio. Then insert the script into the lower Memo, click the Execute button, and get the result in the upper Memo. You can find the Demo1 source on GitHub. The behind the scene details of how Delphi manages to run your Python code in this amazing Python GUI can be found at this link.
The following scripts are used to create a simple database, insert data, and run a simple query using SQLite in Python GUI for Delphi Windows App:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
import sqlite3 con = sqlite3.connect('example.db') cur = con.cursor() # Create table cur.execute('''CREATE TABLE stocks (date text, trans text, symbol text, qty real, price real)''') # Insert a row of data cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)") # Save (commit) the changes con.commit() # We can also close the connection if we are done with it. # Just be sure any changes have been committed or they will be lost. con.close() # The data you’ve saved is persistent and is available in subsequent sessions: con = sqlite3.connect('example.db') cur = con.cursor() # Do this instead t = ('RHAT',) cur.execute('SELECT * FROM stocks WHERE symbol=?', t) print(cur.fetchone()) # Larger example that inserts many records at a time purchases = [('2021-03-28', 'BUY', 'IBM', 1000, 45.00), ('2021-04-05', 'BUY', 'MSFT', 1000, 72.00), ('2021-04-06', 'SELL', 'IBM', 500, 53.00), ] cur.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases) # Retrieve the data using iterator: for row in cur.execute('SELECT * FROM stocks ORDER BY price'): print(row) |
Here is the result in Python GUI:
Congratulations, you have successfully created a simple database, insert data, and run a simple query using SQLite in Python GUI for Delphi Windows App.
Check out Python4Delphi which easily allows you to build Python GUIs for Windows using Delphi.
