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

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

6 Ways To Rapidly Collect Massive Datasets in your Apps

Data Visualization: 5 Ways To Be An Enterprise-Grade Master!

Natural Language Processing: 5 Ways To Use NLP In Your Windows Apps

Machine Learning: 5 Ways To Use ML in your Windows Apps

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 Demo01dproj

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:

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)

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

pip install SQLAlchemy

1	pip install SQLAlchemy

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

System Environment Variable Examples

C:/Users/YOUR_USERNAME/AppData/Local/Programs/Python/Python38/Lib/site-packages
C:/Users/YOUR_USERNAME/AppData/Local/Programs/Python/Python38/Scripts
C:/Users/YOUR_USERNAME/AppData/Local/Programs/Python/Python38

C:/Users/YOUR_USERNAME/AppData/Local/Programs/Python/Python38/Lib/site-packages

C:/Users/YOUR_USERNAME/AppData/Local/Programs/Python/Python38/Scripts

C:/Users/YOUR_USERNAME/AppData/Local/Programs/Python/Python38

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

# Version Check
import sqlalchemy

print(sqlalchemy.__version__)

# Connecting
from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:', echo=True)

# Declare a Mapping
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

    def __repr__(self):
        return "<User(name='%s', fullname='%s', nickname='%s')>" % (
                                self.name, self.fullname, self.nickname)

# Create a Schema
print(User.__table__)
print(User.__tablename__)
print(User.id)
print(User.name)
print(User.fullname)
print(User.nickname)
print(User.__repr__)

Base.metadata.create_all(engine)

# Version Check

import sqlalchemy

print(sqlalchemy.__version__)

# Connecting

from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:', echo=True)

# Declare a Mapping

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column, Integer, String

class User(Base):

__tablename__ = 'users'

id = Column(Integer, primary_key=True)

name = Column(String)

fullname = Column(String)

nickname = Column(String)

def __repr__(self):

return "<User(name='%s', fullname='%s', nickname='%s')>" % (

self.name, self.fullname, self.nickname)

# Create a Schema

print(User.__table__)

print(User.__tablename__)

print(User.id)

print(User.name)

print(User.fullname)

print(User.nickname)

print(User.__repr__)

Base.metadata.create_all(engine)

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:

pip install pymongo

1	pip install 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):

import pymongo

# Create a New Test Database
client = pymongo.MongoClient("localhost", 27017)
db = client.test
print(db.name)
print(db.my_collection)

# Insert New Data
db.my_collection.insert_one({"x": 10}).inserted_id
# Insert New Data
print(db.my_collection.insert_one({"x": 8}).inserted_id)

import pymongo

# Create a New Test Database

client = pymongo.MongoClient("localhost", 27017)

db = client.test

print(db.name)

print(db.my_collection)

# Insert New Data

db.my_collection.insert_one({"x": 10}).inserted_id

# Insert New Data

print(db.my_collection.insert_one({"x": 8}).inserted_id)

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:

pip install mongoengine

1	pip install mongoengine

Here are some MongoEngine Python examples

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

from mongoengine import *
import datetime

connect('mydb')

class BlogPost(Document):
    title = StringField(required=True, max_length=200)
    posted = DateTimeField(default=datetime.datetime.utcnow)
    tags = ListField(StringField(max_length=50))
    meta = {'allow_inheritance': True}

class TextPost(BlogPost):
    content = StringField(required=True)

class LinkPost(BlogPost):
    url = StringField(required=True)

# Create a text-based post
post1 = TextPost(title='Using MongoEngine', content='See the tutorial')
post1.tags = ['mongodb', 'mongoengine']
post1.save()

# Create a link-based post
post2 = LinkPost(title='MongoEngine Docs', url='hmarr.com/mongoengine')
post2.tags = ['mongoengine', 'documentation']
post2.save()

# Iterate over all posts using the BlogPost superclass
for post in BlogPost.objects:
    print('===', post.title, '===')
    if isinstance(post, TextPost):
        print(post.content)
    elif isinstance(post, LinkPost):
        print('Link:', post.url)

# Count all blog posts and its subtypes
print(BlogPost.objects.count())
print(TextPost.objects.count())
print(LinkPost.objects.count())

# Count tagged posts
print(BlogPost.objects(tags='mongoengine').count())
print(BlogPost.objects(tags='mongodb').count())

from mongoengine import *

import datetime

connect('mydb')

class BlogPost(Document):

title = StringField(required=True, max_length=200)

posted = DateTimeField(default=datetime.datetime.utcnow)

tags = ListField(StringField(max_length=50))

meta = {'allow_inheritance': True}

class TextPost(BlogPost):

content = StringField(required=True)

class LinkPost(BlogPost):

url = StringField(required=True)

# Create a text-based post

post1 = TextPost(title='Using MongoEngine', content='See the tutorial')

post1.tags = ['mongodb', 'mongoengine']

post1.save()

# Create a link-based post

post2 = LinkPost(title='MongoEngine Docs', url='hmarr.com/mongoengine')

post2.tags = ['mongoengine', 'documentation']

post2.save()

# Iterate over all posts using the BlogPost superclass

for post in BlogPost.objects:

print('===', post.title, '===')

if isinstance(post, TextPost):

print(post.content)

elif isinstance(post, LinkPost):

print('Link:', post.url)

# Count all blog posts and its subtypes

print(BlogPost.objects.count())

print(TextPost.objects.count())

print(LinkPost.objects.count())

# Count tagged posts

print(BlogPost.objects(tags='mongoengine').count())

print(BlogPost.objects(tags='mongodb').count())

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:

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 :

pip install redis

1	pip install redis

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

import redis

r = redis.StrictRedis(host='localhost', port=6379, db=0)

r.set('foo', 'bar')
print(r.get('foo'))

r.set('Company', 'Embarcadero Technologies')
print(r.get('Company'))

import redis

r = redis.StrictRedis(host='localhost', port=6379, db=0)

r.set('foo', 'bar')

print(r.get('foo'))

r.set('Company', 'Embarcadero Technologies')

print(r.get('Company'))

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.

Build Apps The Smart Way With RAD Studio

Reduce development time and get to market faster with RAD Studio, Delphi, or C++Builder.
Design. Code. Compile. Deploy.
Start Free Trial Upgrade Today

Free Delphi Community Edition Free C++Builder Community Edition