sqlalchemy-iris

Downloads31
Subscribe
3
Bookmark
3
This application is not supported by InterSystems Corporation. Please be notified that you use it at your own responsibility.
Details
Releases
Reviews
Issues
Guillaume Rongier17 November 2022
A major step forward !

It support schema, stream, datetime, read, write !

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("iris://_SYSTEM:SYS@localhost:53126/IRISAPP")

# create fake dataframes with a bunch of columns types
# and a bunch of rows
# columns types are: int, float, string, datetime, bool
df = pd.DataFrame({
    'int': [1, 2, 3, 4, 5],
    'float': [1.1, 2.2, 3.3, 4.4, 5.5],
    'string': ['a', 'b', 'c', 'd', 'e'],
    'datetime': pd.date_range('20130101', periods=5),
    'bool': [True, False, True, False, True]
})

# create a table in IRIS
df.to_sql('iris_table', engine, if_exists='replace', schema='sqlalchemy')

# read the table back from IRIS 
df2 = pd.read_sql('select * from sqlalchemy.iris_table', engine)

# print the dataframe
print(df2)

# print the table types in iris with sql type and class type
sql_def = """
SELECT Tables.TABLE_SCHEMA, Tables.TABLE_NAME, Columns.COLUMN_NAME, Columns.DATA_TYPE, Prop.Type
FROM INFORMATION_SCHEMA.TABLES AS Tables
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS Columns 
    ON (Columns.TABLE_SCHEMA = Tables.TABLE_SCHEMA) AND (Columns.TABLE_NAME = Tables.TABLE_NAME)
INNER JOIN %Dictionary.CompiledProperty AS Prop 
    ON (Prop.parent = Tables.CLASSNAME and Prop.Name = Columns.COLUMN_NAME)
WHERE Tables.TABLE_NAME = 'iris_table' and Tables.TABLE_SCHEMA = 'sqlalchemy'"""

# execute the sql
df3 = pd.read_sql(sql_def, engine)

# print the dataframe
print(df3)

Result :

   index  int  float string             datetime  bool
0      0    1    1.1      a  2013-01-01 00:00:00     1
1      1    2    2.2      b  2013-01-02 00:00:00     0
2      2    3    3.3      c  2013-01-03 00:00:00     1
3      3    4    4.4      d  2013-01-04 00:00:00     0
4      4    5    5.5      e  2013-01-05 00:00:00     1
  TABLE_SCHEMA  TABLE_NAME COLUMN_NAME    DATA_TYPE                     Type
0   sqlalchemy  iris_table       index       bigint          %Library.BigInt
1   sqlalchemy  iris_table         int       bigint          %Library.BigInt
2   sqlalchemy  iris_table       float       double          %Library.Double
3   sqlalchemy  iris_table      string  longvarchar  %Stream.GlobalCharacter
4   sqlalchemy  iris_table    datetime    timestamp        %Library.DateTime
5   sqlalchemy  iris_table        bool          bit         %Library.Boolean
show more
Rating
5 (1)
Category
Frameworks
Works with
InterSystems IRISInterSystems IRIS for Health
Tags
Info
Version
0.6.0
Last updated
2023-03-23
Repository
Open
Documentation
Open
License
Link