Delphi What You Need To Build Object Relational Mapper GUI Apps

[FireWind]

What You Need To Build Object Relational Mapper GUI Apps
By Anbarasan November 3, 2021

Do you want to connect your databases with Object relational mapper capabilities in your Delphi GUI App? This post will get to understand how to use SQLAlchemy Python Library using Для просмотра ссылки Войди или Зарегистрируйсяin Delphi/C++ application. SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. Check out the SQLAlchemy overview Для просмотра ссылки Войди или Зарегистрируйся.

Python for Delphi (P4D) is a set of free components that wrap up the Python DLL into Delphi. They let you easily execute Python scripts, create new Python modules and new Python types. You can use Python4Delphi a number of different ways such as:

• Create a Windows GUI around your existing Python app.
• Add Python scripting to your Delphi Windows apps.
• Add parallel processing to your Python apps through Delphi threads.
• Enhance your speed-sensitive Python apps with functions from Delphi for more speed.

What are the prerequisites to build Object Relational Mapper (ORM) apps?​

• If not python and Python4Delphi is not installed on your machine, Check this Для просмотра ссылки Войди или Зарегистрируйся
• Open windows open command prompt, and type pip install -U SQLAlchemy to install SQLAlchemy. For more info for Installing Python Modules Для просмотра ссылки Войди или Зарегистрируйся
• First, run the Demo1 project for executing Python script in Python for Delphi. Then load the SQLAlchemy sample script in the Memo1 field and press the Execute Script button to see the result. On Clicking Execute Button the script strings are executed using the below code. Go to GitHub to download the Для просмотра ссылки Войди или Зарегистрируйся source.

procedure TForm1.Button1Click(Sender: TObject);
begin
 PythonEngine1.ExecStrings( Memo1.Lines );
end;

Is there an example or sample SQLAlchemy ORM Python script?​

Here are the SQLAlchemy Python Library sample script details:

• Create an SQLAlchemy application object called the Engine. This object acts as a central source of connections to a particular database, providing both a factory as well as a holding space called a connection pool for these database connections. Here SQLLite database is used to connect.
• SQLAlchemy Expression Language that is the primary feature of SQLAlchemy, make use of one simple construct within this package called the text() construct, which allows us to write SQL statements as textual SQL.
• Create some_table and insert values into it, commit the transaction, retrieve the data and print it.
• Above were executed sql scripts using connection and the same can be performed by Executing with an ORM session.
• Creation of metadata, table ,column and adding constraints were included. Apart from these much more can be done such as table reflection, data manipulation ,etc. using SQLAlchemy library.

from sqlalchemy import create_engine
from sqlalchemy import text
#Engine Creation
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)
with engine.connect() as conn:
    result = conn.execute(text("select 'hello world'"))
    print(result.all())
#Commit changes
with engine.connect() as conn:
# Table Creation
    conn.execute(text("CREATE TABLE some_table (x int, y int)")) 
# Value insertion
    conn.execute(text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
       [{"x": 1, "y": 1}, {"x": 2, "y": 4}]
    )
# Commiting the transaction
    conn.commit()
# print the commited values
    result = conn.execute(text("SELECT x, y FROM some_table"))
    for row in result:
        print(f"x: {row.x}  y: {row.y}")
# sending parameters and printing the result
    result = conn.execute(
        text("SELECT x, y FROM some_table WHERE y > :y"),
        {"y": 1}
    )
    for row in result:
        print(f"x: {row.x}  y: {row.y}")
# Using ORM Session to insert data to the database
from sqlalchemy.orm import Session
 
stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y").bindparams(y=1)
with Session(engine) as session:
    result = session.execute(stmt)
    for row in result:
        print(f"x: {row.x}  y: {row.y}")
# Working with metadata or schema
from sqlalchemy import MetaData
metadata = MetaData()
from sqlalchemy import Table, Column, Integer, String
user_table = Table(
    "user_account",
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(30)),
    Column('fullname', String)
)
print(user_table.c.name)
print(user_table.c.keys())

SQLAlchemy Demo

Note: Samples used for demonstration were picked from Для просмотра ссылки Войди или Зарегистрируйся with only the difference of printing the outputs to the Memo. You can check the APIs and some more samples from the same place. You can show the table values to Для просмотра ссылки Войди или Зарегистрируйсяas well.