SQLAlchemy - the easiest way to use Python and SQL with IRIS's databases
Tags: tutorial, python, beginners, sql
Published: 2023-04-16 08:08:47

## Introduction

In some of the last few articles I've talked about types between IRIS and Python, and it is clear that it's not that easy to access objects from one side at another. 

Fortunately, work has already been done to create [SQLAlchemy-iris](https://openexchange.intersystems.com/package/sqlalchemy-iris) (follow the link to see it on Open Exchange), which makes everything much easier for Python to access IRIS' objects, and I'm going to show the starters for that.

Thank you @Dmitry.Maslennikov<span> !</span>

 

## Installing

To install, simply open your terminal with administrators' access and type

<pre class="codeblock-container" idlang="12" lang="Shell Session" tabsize="4"><code class="language-shell hljs">pip install sqlalchemy-iris</code></pre>

and that will also install the pre-requisites for you, if needed.

 

## Usage

Now, on a python file, you can import the module, connect to the database and play with sqlalchemy in any way you want. If you feel comfortable, you may follow the steps:

 

  * Import "create\_engine" from sqlalchemy and create the engine with the string "iris://username:password@IP:port/namespace". Of course you can import the whole module, but "create\_engine" creates an instance of Engine (sqlalchemy.engine, for more info click [here](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Engine)) has all the subclasses necessary for what I'm presenting here.

``` 
from sqlalchemy import create_engine

engine = create_engine("iris://_SYSTEM:SYS@localhost:1972/SAMPLE") 
```
  

* Create the connection (sqlalchemy.engine.connection, for more info click [here](https://docs.sqlalchemy.org/en/20/core/connections.html#index-0)) so you can work with transactions, simple execute, etc.

<pre class="codeblock-container" idlang="2" lang="Python" tabsize="4"><code class="language-python hljs">conn = engine.connect()</code></pre>

Great! Now you have configured access to your database.

 

For a simple SELECT query, and to iterate through the result set, do the following (as usual, using as example the table created in [this article](https://community.intersystems.com/post/quick-sample-database-tutorial)):

``` query = 'SELECT Name, Age  from Sample.PersistentData WHERE Age >=21'
result = conn.exec_driver_sql(query) ```

Now, _result_ is a  CursorResult (sqlalchemy.engine.CursorResult). You can check everything you can do with a CursorResult [here](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.CursorResult), in the official documentation. This is all you have to do for a simple iteration:

```
print("Name, Age")
for row in result:
    print(row[0], ", ", row[1])
```

 and with a little formatting you may have an output like this:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z15mgo04rqbbbwn5amh4.png)

 

You can also check [the official documentation](https://docs.sqlalchemy.org/en/20/index.html) for more details and possibilities, now that you know where to start.

 

PS.: you can also import "text" from sqlalchemy and execute the query as

<pre class="codeblock-container" idlang="2" lang="Python" tabsize="4"><code class="language-python hljs">result = conn.execute(text(query))</code></pre>

which will produce the exact same results.

 

 

## Conclusion

You can also perform DDL and any other DML statements, and there's even bigger support to work with ORM (_Object Relational Mapping), _but since this is just a "kick off" I'm not going further.

Would you like more tutorials with SQLAlchemy? Maybe next time using ORM?  
Feel free to contact me for any doubts!