I want to convert the data types of the results of a SQL query run using create_engine from SQLAlchemy to a Python object, save it as a CSV and than load into another SQL database, which may use a different dialect. As I have to deal with queries that may return a large number of rows, the data might be load in chunks, and in some cases the automatic type definition made by tools such as Pandas are not appropriate (e.g.: is the first chunk have all rows have None as value, but the next have integers texts, and another one have only strings, but they are in fact only strings).
I tried to use SQLAlchemy.inspect, but it works only for tables and not for the results of queries that have joins, such as:
SELECT users.id, COUNT(*) AS total_users_orders, SUM(orders.price) AS lifetime_value
FROM users
INNER JOIN orders
ON orders.user_id = users.id
GROUP BY orders.id
In this case I want to have access to the data type of user_id (SQL: INT, Python: int) and total_users_orders (SQL: INT, Python: int) and lifetime_value (SQL: FLOAT, Python: float).
There is any way to get the data types of the rows returned by this query and map then to python types without relying on Pandas automatically inferring?