Do you want to add the full power, functionalities, and flexibilities of SQL to your Python GUI App? You can build it easily by combining SQLAlchemy and Python4Delphi library, inside Delphi and C++Builder.
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.
Table of Contents
Hands-On
This post 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.
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.
Let’s perform some sanity check and introductory examples of the SQLAlchemy library:
|
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 40 |
# 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) |
The result in Python GUI:
Congratulations, now you have learned how to run various SQL and Object Relational Mapper operations using Python’s SQLAlchemy library and connect it to the Delphi Windows GUI app using Python for Delphi.
Check out the SQLAlchemy library for Python and use it in your projects: https://pypi.org/project/SQLAlchemy/ and
Check out Python4Delphi which easily allows you to build Python GUIs for Windows using Delphi: https://github.com/pyscripter/python4delphi
