We are happy to announce the launch of our new product line, Devart Python connectors. After months of hard work and dedication, we released reliable connectivity solutions for accessing database servers and cloud services from Python applications. The connectors fully implement the Python Database API Specification v2.0 and are available as wheel packages for Windows, macOS, and Linux.
This release includes connectors for the following data sources:
- Database connectors: ASE, dBase, Firebird, Google BigQuery, InterBase, MariaDB, MySQL, Oracle, PostgreSQL, SQLite, SQL Server, VisualFoxPro, and various xBase databases.
- Cloud connectors: BigCommerce, Dynamics 365, HubSpot, NetSuite, QuickBooks, Salesforce, Zoho CRM.
The following sections cover the broad set of capabilities in our Python connectors.
Direct Connection
The solution lets you connect to relational and non-relational databases directly through TCP/IP, eliminating the need for database client libraries. A direct connection also increases the speed of data transmission between a Python application and the database server.
For example, to connect to Oracle Database, you don’t have to install Oracle Instant Client on the workstation—enable the Direct mode as in the following code snippet.
import devart.oracle
connection = devart.oracle.connect(
Direct=True,
Host="dboracle",
Port=1521,
ServiceName="orcl1120",
UserName="scott",
Password="tiger"
)
Cloud data sources are accessed through HTTPS—you don’t need additional client software on the user workstation.
Standard SQL Syntax in Cloud Connectors
The connectors conform to the Python DB API 2.0 specification and fully support the ANSI SQL-92 standard. You can execute SQL statements against your cloud data just like you would normally work with relational databases, including complex queries containing different types of JOINs, ORDER BY and GROUP BY clauses, and aggregate functions.
Simple queries are directly converted to API calls and executed on the cloud service side. Complex queries are transformed into simpler queries, which are then converted to API calls. The embedded SQL engine then processes the results in the local cache and applies advanced SQL features from the original complex query.
The following example shows a complex query run against Microsoft Dynamics 365.
import dynamics365
connection = dynamics365.connect(
Server="https://mydinamics.crm4.dynamics.com/",
UserId="[email protected]",
Password="my_password"
)
sql = "
Select a.customersizecode,
a.territorycode,
a.name,
a.accountnumber,
at.isdocument,
at.businessunitname
From account a
Left Join (Select ant.objectid,
ant.subject,
ant.isdocument,
b.name as businessunitname
From annotation ant
Left Join (Select * From businessunit) b
On ant.owningbusinessunit = b.businessunitid
) at
On at.objectid = a.accountid
Where
a.exchangerate = 1 And
a.websiteurl Is not Null
Order By
a.revenue,
a.versionnumber,
a.statecode
"
cursor = connection.cursor()
cursor.execute(sql)OAuth 2.0 Authorization in Cloud Connectors
You will enjoy the ease of OAuth 2.0 authorization to cloud services that support this authorization framework: Salesforce, BigCommerce, Zoho CRM, and others.
import devart.salesforce
dict = devart.salesforce.signin(host="login.salesforce.com")
connection = devart.salesforce.connect(
Authentication="OAuth",
Host="login.salesforce.com",
RefreshToken=dict["Refresh Token"]
)Connection Pooling
Connection pooling allows you to reduce the cost of opening and closing connections for each operation by maintaining a pool of pre-established connections to a database or cloud service.
Connection pooling configuration is quite flexible, but if you want to enable pooling with default settings, you can do that with one line of code.
devart.postgresql.connection_pool.enabled = TrueSQLite Database Encryption
The Python Connector for SQLite supports native SQLite database encryption without requiring you to purchase an encryption extension for SQLite. This functionality is available in the Direct mode, which uses a statically linked SQLite library.
You can choose from the following encryption algorithms to protect your data from unauthorized access: Triple DES, Blowfish, AES-128, AES-192, AES-256, CAST-128, and RC4.
To encrypt a database, you execute the following PRAGMA statements.
connection = devart.sqlite.connect("Direct=True;Database=your_database;")
cursor = connection.cursor()
cursor.execute("PRAGMA ENCRYPTION=AES256")
cursor.execute("PRAGMA REKEY='your_key'")You specify the encryption key and algorithm to connect to the encrypted database.
connection = devart.sqlite.connect("Direct=True;Database=your_database;EncryptionAlgorithm=AES256;EncryptionKey=your_key")xBase Compatibility
Python Connector for xBase supports the xBase family of databases, covering the following database formats: Visual FoxPro, FoxPro 2, dBase III – dBase 7, Clipper, Codebase, and HiPer-Six.
Although dBASE and derived databases get few new users, many companies established in the last century still maintain and actively use them daily. The connector can retrieve and update data in various xBase databases. If your company wants to migrate its data to a more modern database, the connector can help implement the migration routines.
Our connector provides parameters for ignoring corrupted data and metadata errors in DBF tables. Corrupted data is skipped, while intact data is properly retrieved, which lets you access the data you thought were long lost.
The solution supports the standard ANSI SQL-92 syntax and offers an internal data indexing mechanism that is way more efficient than native DBF indexes for complex queries.
Support for ETL Tools
The connectors are compatible with popular Python ETL tools: petl, pandas, and SQLAlchemy. Among these, only petl is a general-purpose Python package for extracting, transforming, and loading data tables, but complex real-world ETL scenarios often involve all three tools.
