Interacting with a relational database

[ztsweet]

API likes pandas.read_sql_query(sql, con, ...) and df.to_sql(name, con, ...)

[samukweku]

Not sure if it is sth that should be implemented here … the maintenance burden I feel will increase significantly… maybe it should be built in datatable but as another library …

[st-pasha]

Frankly, it's not that complicated. The sqlalchemy library provides interfaces for various dbs, and we just need to convert that into a frame object. Something like this works:

>>> conn = sqlalchemy.create_engine('sqlite:///example.db').connect()
>>> result = conn.execute("SELECT * FROM stocks")
>>> dt.Frame([row._data for row in result], names=list(result.keys()))
   | date        trans  symbol      qty    price
   | str32       str32  str32   float64  float64
-- + ----------  -----  ------  -------  -------
 0 | 2006-01-05  BUY    RHAT        100    35.14
 1 | 2006-01-05  BUY    MOON      10000    35.14
 2 | 2006-01-05  BUY    GOOG     100000    35.14
[3 rows x 5 columns]

And yes, we can make dt.Frame(result) work automatically, to avoid unnecessary boilerplate. The only thing that needs to be worked out is how to get column types from the ResultProxy. It was supposed to be [desc[1] for desc in result.cursor.description], but I'm getting just Nones here, not sure whether it's a deficiency of sqlite db driver, or I'm doing something wrong.

[ztsweet]

@st-pasha I think those API is more commonly used, but my development ability is weaker....

[samukweku]

If there is much interest in this I would def love to contribute. @ztsweet u can start… u will get better at it