asynchronous twisted pandas

I am a big fan of twisted(asynchronous) protocol, I predominantly use cyclone.io for my most of my project related work that require a non blocking io support. In general python community has good coverage of twisted support for various packages.

Having said that, when one work with such non blocking web framework, if the desired twisted supported library is not available for well known package then he/she has to give up those packages from their stack which is obviously pain for most of the developer.

Recently I came across such situation and had to give up such famous library is python pandas. Pandas is not one of those libraries to easily give up especially due to its elegance and performance that it offers for mostly data science related work. Recently I have done plenty of such prototype work with pandas and executed successfully as well.That was one of the reason i decided to take its sql functionality to production for one such use case for my work.

So, I had personally raised this issue with pandas team and expecting to get holistic implementation of pandas.io.sql package in twisted way. I knew it is not easy job though.

Meantime, i had to figure out a workaround to the above said problem. The below code explains that and how one vision such requirement.

Feel free to provide your comment.

Code start here —

from alchimia import TWISTED_STRATEGY</code>

from sqlalchemy import (
create_engine, MetaData, Table, Column, Integer, String
)
from sqlalchemy.schema import CreateTable

from twisted.internet import defer
from twisted.internet.task import react

from pandas.io.sql import *
import pandas as pd

from sqlalchemy.pool import QueuePool

class MyPandasSQLAlchemy(PandasSQLAlchemy):

@defer.inlineCallbacks
def execute(self, *args, **kwargs):
"""Simple passthrough to SQLAlchemy engine"""
result = yield self.engine.execute(*args, **kwargs)
defer.returnValue(result)

@defer.inlineCallbacks
def main(reactor):
engine = create_engine(
"sqlite:///file.db", poolclass=QueuePool, reactor=reactor, strategy=TWISTED_STRATEGY
)

metadata = MetaData()
users = Table("users", metadata,
Column("id", Integer(), primary_key=True),
Column("name", String()),
)

'''
# Create the table
yield engine.execute(CreateTable(users))

# Insert some users
yield engine.execute(users.insert().values(name="Jeremy Goodwin"))
yield engine.execute(users.insert().values(name="Natalie Hurley"))
yield engine.execute(users.insert().values(name="Dan Rydell"))
yield engine.execute(users.insert().values(name="Casey McCall"))
yield engine.execute(users.insert().values(name="Dana Whitaker"))

'''

query = "SELECT * FROM users"

pandas_sql = MyPandasSQLAlchemy(engine, meta=metadata)
df = yield pandas_sql.execute(query)
df_users = yield df.fetchall()

df = pd.DataFrame(df_users)
print df.head()

'''
# raw alchemy way
result = yield engine.execute(query)
d_users = yield result.fetchall()
# Print out the users
for user in d_users:
print "Username: %s" % user[users.c.name]

'''

if __name__ == "__main__":
react(main, [])