The processing of data is the soul of computing. Almost every single computer ever invented was designed so that human beings could take information in the form of data and manipulate it in one way or another, either through visualizations or use it in other ways to make decisions about our lives, work, and future. If data processing is the soul, then the pieces of data itself are the bones we use to build the body of computing. In this article, we’ll show you how to access, manipulate, and master those building blocks, including how to use Python Windows GUI builder to add features and functionalities to Python; how to use Python libraries to perform cryptographic tasks; how to connect SQLite Database in Python4Delphi, and much more.
Table of Contents
What do people mean when they call something a database?
According to Oracle, who specializes in the field, a Database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS) which makes it easier to create, and generally control the database’s data and the programs which make the database work. Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just database.
The evolution of the database
- The 1960s: Navigational databases such as the hierarchical database (which relied on a tree-like model and allowed only a one-to-many relationship), and the network database (a more flexible model that allowed multiple relationships), were the original systems used to store and manipulate data. Although simple, these early systems were inflexible.
- The 1980s: Relational databases became popular.
- The 1990s: Object-oriented databases became popular.
- The 2000s – Now: NoSQL databases came about as a response to the growth of the internet and the need for faster speed and processing of unstructured data. Today, cloud databases and self-driving databases are breaking new ground when it comes to how data is collected, stored, managed, and utilized.
What types of databases are there?
To make a better decision about the types of databases you need, first, you need to learn more about the different kinds of databases.
Below are some common types of databases you may encounter either during your personal life or in your business, as collected by Indeed Editorial Team:
- Centralized database
- Cloud database
- Commercial database
- Distributed database
- End-user database
- Relational database
- NoSQL database
- Graph database
- Object-oriented database
- Open-source database
- Operational database
- Personal database
Why use Python for working with databases?
- Python is perfectly fine for most applications for creating database clients.
- Python provides a vast set of libraries to work with an extensive variety of databases.
- Python is good for rapid prototyping.
Read more here, to see “How Python is Ideal for Handling Data Intensive Tasks”:
Delphi adds powerful GUI features and functionalities to Python
In this tutorial, we’ll build Windows Apps with extensive Database capabilities by integrating Python’s Database libraries with Embarcadero’s Delphi, using Python4Delphi (P4D).
P4D empowers Python users with Delphi’s award-winning VCL functionalities for Windows which enables us to build native Windows apps 5x faster. This integration enables us to create a modern GUI with Windows 10 looks and responsive controls for our Python Database applications. Python4Delphi also comes with an extensive range of demos, use cases, and tutorials.
We’re going to cover the following…
How to use SQLite, SQLAlchemy, pymongo, mongoengine, redis-py, and FireDac Python libraries to interact with databases
All of them would be integrated with Python4Delphi to create Windows Apps with Database capabilities.
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 the Getting Started With Python4Delphi video by Jim McKeeth.
Time to get Started!
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.
How do I connect to a SQLite Database in Python4Delphi?
Do you want to add the full power, functionalities, and flexibilities of SQL to your Python GUI or Windows App, 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.
Here is a code example of using SQLite to create a simple database, insert data, and run a simple query
The following is a code example of SQLite to create a simple database, insert data, and run a simple query. You should run this inside the lower Memo of Python4Delphi Demo01 GUI:
[crayon-6741ba56b7c95949754868/]Here is the result of accessing the SQLite database 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!
How do I connect to a MySQL Database in Python using SQLAlchemy?
SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. SQLAlchemy provides a full suite of well-known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.
The following are Major SQLAlchemy features:
- An industrial strength ORM, built from the core on the identity map, unit of work, and data mapper patterns. These patterns allow the transparent persistence of objects using a declarative configuration system. Domain models can be constructed and manipulated naturally, and changes are synchronized with the current transaction automatically.
- A relationally-oriented query system, exposing the full range of SQL’s capabilities explicitly, including joins, subqueries, correlation, and most everything else, in terms of the object model. Writing queries with the ORM uses the same techniques of relational composition you use when writing SQL. While you can drop into literal SQL at any time, it’s virtually never needed.
- A comprehensive and flexible system of eager loading for related collections and objects. Collections are cached within a session and can be loaded on individual access, all at once using joins, or by query per collection across the full result set.
- A Core SQL construction system and a DBAPI interaction layer. The SQLAlchemy Core is separate from the ORM and is a full database abstraction layer in its own right, and includes an extensible Python-based SQL expression language, schema metadata, connection pooling, type coercion, and custom types.
- All primary and foreign key constraints are assumed to be composite and natural. Surrogate integer primary keys are of course still the norm, but SQLAlchemy never assumes or hardcodes to this model.
- Database introspection and generation. Database schemas can be “reflected” in one step into Python structures representing database metadata; those same structures can then generate CREATE statements right back out – all within the Core, independent of the ORM.
This section will guide you on how to run various SQL and Object Relational Mapper operations using Python’s SQLAlchemy library and then display it in the Delphi Windows GUI app using Python for Delphi.
Installing SQLAlchemy in Python
After installing Python4Delphi properly, you can get SQLAlchemy using pip or easy install to your command prompt:
[crayon-6741ba56b7ca0932871025/]Don’t forget to put the path where your SQLAlchemy library installed, to the System Environment Variables:
System Environment Variable Examples
[crayon-6741ba56b7ca2215851035/]Let’s perform some introductory examples of the SQLAlchemy library:
[crayon-6741ba56b7ca4353357153/]Here are the SQLAlchemy introductory examples in the Python GUI
How to connect to MongoDB using PyMongo + Python4Delphi
The PyMongo distribution contains tools for interacting with the MongoDB database from Python. The requirement of this library is, you need to install MongoDB on your computer first. You can easily install MongoDB in your Windows OS, by following these guides:
- Install MongoDB on Windows
- Install MongoDB Community on Windows using msiexec.exe
- Install MongoDB Community Edition on Windows
The PyMongo package is a native Python driver for MongoDB. PyMongo supports MongoDB 2.6, 3.0, 3.2, 3.4, 3.6, 4.0, 4.2, and 4.4.
The bson package is an implementation of the BSON format for Python. The gridfs package is a gridfs implementation on top of PyMongo.
Installing PyMongo
First, here is how you can get PyMongo:
[crayon-6741ba56b7ca6862090253/]Below is the code for Creating New Database, Connect to It, Insert New Data, and Find Them using PyMongo (Run the following code inside the lower Memo of Python4Delphi Demo01 GUI):
[crayon-6741ba56b7ca8094698857/]PyMongo Python4Delphi Results
How to use MongoEngine?
MongoEngine is a Python Object-Document Mapper for working with MongoDB. MongoEngine is based on the PyMongo driver and tested against multiple versions of MongoDB.
MongoEngine is currently tested against MongoDB v3.4, v3.6, and v4.0. Future versions should be supported as well, but aren’t actively tested at the moment.
Step one, installing MongoEngine
First, here is how you can get MongoEngine:
[crayon-6741ba56b7ca9741017110/]Here are some MongoEngine Python examples
Run these simple examples of MongoEngine code inside the lower Memo of Python4Delphi Demo01 GUI:
[crayon-6741ba56b7cab708466441/]MongoEngine Simple Examples
How do I use Redis in Python?
redis-py is the Python interface to the Redis key-value store. Redis is an open source (BSD licensed), in-memory data structure store, used as a database, cache, and message broker. Redis provides data structures such as strings, hashes, lists, sets, sorted sets with range queries, bitmaps, hyperloglogs, geospatial indexes, and streams.
redis-py requires a running Redis server. You can easily install Redis in your Windows OS, by following these guides:
- How To Install Redis on Windows 10, or
- How To Install Redis on Windows 10 in 2020
- You can get the installer here: microsoftarchive/redis
Installing Redis in Python
Here is how you can install redis :
[crayon-6741ba56b7cad642565862/]Run the following code to Getting Started with redis-py:
[crayon-6741ba56b7caf004895487/]Here is the redis-py Demo Result in the Python GUI:
Bonus: How to use FireDAC in Python4Delphi
FireDAC is a unique and very comprehensive set of Universal Data Access Components for developing multi-device database applications for Delphi and C++Builder. With its powerful common architecture, FireDAC enables native high-speed direct access from Delphi to Interbase, SQLite, MySQL, SQL Server, Oracle, PostgreSQL, IBM DB2, SQL Anywhere, Access, Firebird, Informix, and more.
FireDAC is a powerful, yet easy-to-use access layer that supports, abstracts, and simplifies data access, providing all the features needed to build real-world high-load applications. FireDAC provides a common API for accessing different database back-ends, without giving up access to unique database-specific features and without compromising on performance. Use FireDAC in Windows, Android, iOS, and Mac OS X applications you are developing for PCs, tablets, and smartphones.
You can easily connect Python with FireDAC through Python4Delphi Demo.
The best advantage of P4D is, it makes it very easy to use Python as a scripting language for Delphi applications that comes with a very extensive range of customizable demos and tutorials that are ready to use to develop real-world apps, as we’ve mentioned earlier. In shorts: P4D gives you various real-world “Hello World!” apps that are ready to adjust to your needs!
An example of Demo10-Python4Delphi VCL with FireDAC Database
Conclusion
We have shown you 6 powerful Python libraries for interacting with popular databases ranging from SQL, NoSQL, MongoDB and Redis as well as how to use FireDAC with Python. All of those really great tools for accessing pretty much any database were wrapped well inside a powerful GUI provided by Python4Delphi. We can’t wait to see what you build with Python4Delphi!
Want to know some more? Then check out Python4Delphi which easily allows you to build Python GUIs for Windows using Delphi.
