Do you want to connect your databases with Object-relational mapper capabilities in your Python Build Tools? This post will get to understand how to use SQLAlchemy Python Library using Python4Delphi 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 here.
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 in 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.
Table of Contents
What are the prerequisites to build Object Relational Mapper (ORM) apps?
- If not python and Python4Delphi is not installed on your machine, Check this how to run a simple python script in Delphi application using Python4Delphi sample app
- Open windows open command prompt, and type pip install -U SQLAlchemy to install SQLAlchemy. For more info for Installing Python Modules check here
- 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 Demo1 source.
|
1 2 3 4 |
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.
|
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 41 42 43 44 45 46 47 48 49 |
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()) |
Note: Samples used for demonstration were picked from here 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 grids as well.
You have read the quick overview of SQLAlchemy library, download this library from here, and connect to the database with object relational mapper capabilities in your applications. Check out Python4Delphi and easily build Python GUIs for Windows using Delphi.
