6 Easy Ways to Connect to the Top Databases You Should Know

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 to use 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’re going to take you on a tour of different ways to access, manipulate and master those building blocks.

What do people mean when they call something a database?

According to Oracle who specialize 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?

  1. Python is perfectly fine for most applications for creating database clients.
  2. Python provides a vast set of libraries to work with an extensive variety of databases.
  3. 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.

python4delphi run demo01
Open Demo01.dproj.

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:

Here is the result of accessing the SQLite database in Python GUI

1_demo_sqlite
SQLite Demo with Python4Delphi in Windows.

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:

Don’t forget to put the path where your SQLAlchemy library installed, to the System Environment Variables:

System Environment Variable Examples

Let’s perform some introductory examples of the SQLAlchemy library:

Here are the SQLAlchemy introductory examples in the Python GUI

2_demo_sqlalchemy
SQLAlchemy Demo with Python4Delphi in Windows.

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:

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:

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):

PyMongo Python4Delphi Results

3_demo_pymongo1
PyMongo Demo with Python4Delphi in Windows.

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:

Here are some MongoEngine Python examples

Run these simple examples of MongoEngine code inside the lower Memo of Python4Delphi Demo01 GUI:

MongoEngine Simple Examples

4_demo_mongoengine
MongoEngine Demo with Python4Delphi in Windows.

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:

Installing Redis in Python

Here is how you can install redis :

Run the following code to Getting Started with redis-py:

Here is the redis-py Demo Result in the Python GUI:

5_demo_redis
redis-py Demo with Python4Delphi in Windows.

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.

6_firedac
FireDAC: Universal Enterprise Data Connectivity

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

6_demo_firedac
FireDAC Demo with Python4Delphi in Windows.

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.