Connecting to InterBase from Python
Go Up to Main Page
The InterBase Python driver allows developers to access InterBase RDBMS. This driver supports the Python Database API 2.0 standard (PEP-249) while offering extended access to the native InterBase API.
Contents
Features
- PEP-249 compliance
- Full Unicode and character set support
- Native API access
- Multiple independent transactions per connection
- Distributed transaction support
- Automatic conversion of textual data
- Prepared statement support
Installation
You can install the InterBase Python driver in the following ways:
Installing via PIP:
pip install interbase
Install from the Github repository:
pip install git+https://github.com/Embarcadero/InterBasePython.git # or via SSH: pip install git+ssh://[email protected]/Embarcadero/InterBasePython.git
You can access the InterBase Python driver repository at: https://github.com/Embarcadero/InterBasePython
Connection Example
This section provides a simple connection example and query, for more details on all the available options for the InterBase Python driver refer to the documentation provided with the driver at https://github.com/Embarcadero/InterBasePython/tree/main/docs.
Creating a Sample Database
For this example, create a sample database called 'pythontest' and fill it with some records. You can use the following script to create the database and insert records.
--Create the database and provide access credentials, you can set a different location or credentials if required.
CREATE DATABASE 'pythontest.ib' USER 'SYSDBA' PASSWORD 'masterkey';
--Connect to the database and provide access credentials.
CONNECT 'pythontest.ib' USER 'SYSDBA' PASSWORD 'masterkey';
--Create a simple table called 'employee'.
CREATE TABLE employee (first_name char(25), last_name char(25), country char(25), hire_date DATE);
--Insert records into the 'employee' table.
INSERT INTO employee (first_name, last_name, country, hire_date) VALUES ('Allistair','Charles','Spain','Feb 17, 2019');
INSERT INTO employee (first_name, last_name, country, hire_date) VALUES ('Xantha','Shepherd','Philippines','Feb 12, 2014');
INSERT INTO employee (first_name, last_name, country, hire_date) VALUES ('Kalia','Pace','Australia','Mar 9, 2023');
INSERT INTO employee (first_name, last_name, country, hire_date) VALUES ('Paula','Coleman','Canada','Nov 9, 2017');
INSERT INTO employee (first_name, last_name, country, hire_date) VALUES ('Mira','Blanchard','France','Feb 11, 2020');
INSERT INTO employee (first_name, last_name, country, hire_date) VALUES ('Sydney','Rowland','Ireland','Dec 6, 2017');
INSERT INTO employee (first_name, last_name, country, hire_date) VALUES ('Ferdinand','Murray','Mexico','Jun 27, 2019');
INSERT INTO employee (first_name, last_name, country, hire_date) VALUES ('Ryder','Velazquez','Indonesia','May 3, 2021');
INSERT INTO employee (first_name, last_name, country, hire_date) VALUES ('Tad','Pratt','South Africa','Aug 22, 2024');
INSERT INTO employee (first_name, last_name, country, hire_date) VALUES ('Armando','Sexton','United States','Jul 26, 2014');
This script will create the database on the same location where it is run. You can set a different location on the CREATE DATABASE and CONNECT statements
Save the file as 'create-test-db.sql' and run isql -i <script> to create the database.
isql -i create-test-db.sql
Creating the Python Script
Import the the InterBase Python driver.
import interbase
Set the connection details, provide a user and password for connecting to the database, also provide the location for the database file. This example assumes both the database file and the python script are on the same location. If required set a different location on the 'dsn' variable
user = 'sysdba'
password = 'masterkey'
dsn = "pythontest.ib"
Connect to the database and create a cursor object.
connection = interbase.connect(dsn=dsn, user=user, password=password)
cursor = connection.cursor()
Execute a query and print the results.
cursor.execute("SELECT * FROM EMPLOYEE")
for row in cursor:
print(row)
Ensure to close both the cursor and the connection.
cursor.close()
connection.close()
Full code sample:
import interbase
user = 'sysdba'
password = 'masterkey'
dsn = "pythontest.ib"
connection = interbase.connect(dsn=dsn, user=user, password=password)
cursor = connection.cursor()
cursor.execute("SELECT * FROM EMPLOYEE")
for row in cursor:
print(row)
cursor.close()
connection.close()
After running the script, the results will print as a set of lists.
('Allistair ', 'Charles ', 'Spain ', datetime.date(2019, 2, 17))
('Xantha ', 'Shepherd ', 'Philippines ', datetime.date(2014, 2, 12))
('Kalia ', 'Pace ', 'Australia ', datetime.date(2023, 3, 9))
('Paula ', 'Coleman ', 'Canada ', datetime.date(2017, 11, 9))
('Mira ', 'Blanchard ', 'France ', datetime.date(2020, 2, 11))
('Sydney ', 'Rowland ', 'Ireland ', datetime.date(2017, 12, 6))
('Ferdinand ', 'Murray ', 'Mexico ', datetime.date(2019, 6, 27))
('Ryder ', 'Velazquez ', 'Indonesia ', datetime.date(2021, 5, 3))
('Tad ', 'Pratt ', 'South Africa ', datetime.date(2024, 8, 22))
Showing Formatted Results
You can show results formatted as a table using libraries like tabulate, PrettyTable, Texttable, or Pandas. The following sample code uses PrettyTable to display results as a table.
Ensure PrettyTable is installed.
pip install prettytable
Run the modified script:
from prettytable import PrettyTable
import interbase
# Connection details
user = 'sysdba'
password = 'masterkey'
dsn = "pyhtontest.ib"
# Connect to the database
connection = interbase.connect(dsn=dsn, user=user, password=password)
# Create a cursor object
cursor = connection.cursor()
# Execute a query
cursor.execute("SELECT * FROM EMPLOYEE")
# Set the table headings
tbl= PrettyTable(['First Name', 'Last Name', 'Country', 'Hire Date'])
#Fetch results, add them to the table and print the result.
for row in cursor.fetchall():
tbl.add_row([row[0],row[1],row[2],row[3]])
print(tbl)
# Close the connection
cursor.close()
connection.close()
Output should look something like this:
+---------------------------+---------------------------+---------------------------+------------+ | First Name | Last Name | Country | Hire Date | +---------------------------+---------------------------+---------------------------+------------+ | Allistair | Charles | Spain | 2019-02-17 | | Xantha | Shepherd | Philippines | 2014-02-12 | | Kalia | Pace | Australia | 2023-03-09 | | Paula | Coleman | Canada | 2017-11-09 | | Mira | Blanchard | France | 2020-02-11 | | Sydney | Rowland | Ireland | 2017-12-06 | | Ferdinand | Murray | Mexico | 2019-06-27 | | Ryder | Velazquez | Indonesia | 2021-05-03 | | Tad | Pratt | South Africa | 2024-08-22 | +---------------------------+---------------------------+---------------------------+------------+