sqlalchemy to carray

gistfile1.py

import sqlalchemy
import carray
from sqlalchemy.types import *

BUFFER_SIZE = 100

dtype_map = [
	(BigInteger, "i"),
	(SmallInteger, "i"),
	(Boolean, "i"),
	(Date, None),
	(DateTime, None),
	(Float, "f"),
	(Integer, "i"),
	(Interval, None),
	(LargeBinary, None),
	(String, "S100"),
	(Text, "S100"),
	(Time, None),
	(Unicode, "S100"),
	(UnicodeText, "S100") ]

def dtype_for_column(col):
	for sqltype, dtype in dtype_map:
		if dtype and isinstance(col.type, sqltype):
			return dtype

	raise TypeError("Unknown or unsupported data type '%s'" % col.type)

def ctable_from_table(table):
	arrays = []

	for col in table.columns:
		dtype = dtype_for_column(col)
		arrays.append(carray.carray([], dtype=dtype))

	names = [c.name for c in table.columns]
	ctable = carray.ctable(arrays, names=names)

	result = table.select().execute()

	while 1:
		many = result.fetchmany(BUFFER_SIZE)
		if not many:
			break
		for row in many:
			ctable.append(list(row))

	return ctable
		
URL = "sqlite:///data.sqlite"

engine = sqlalchemy.create_engine(URL)
md = sqlalchemy.MetaData(bind=engine)
md.reflect()
table = sqlalchemy.Table("irbd_balance", md)

t = ctable_from_table(table)
print t