Infer column type SQLAlchemy

Question

Suppose I am using SQLAlchemy to upload a generic .csv file to an mssql server.

How can I infer the type of each column? It seems that one has to declare a column explicitly eg {'myvar' : Column(Integer)}. Is there a way for SQLAlchemy, or some other library, to infer the datatype of each column by itself?

VB has the function "TransferText" which can do just that , but I want to do this in python.

If you have a class YourClass then you can check YourClass.your_attribute.type.python_type — fedorqui
– fedorqui, Commented Jul 5, 2016 at 10:20
Unfortunately that won't work in my case. A generic .csv file contains no type or class information - only comma seperated strings. I can read the header to get the number and name of columns, and make a class where each parameter by default has type None, or String - but I can't infer if a string might represent another data type. — Dr. John A Zoidberg
– Dr. John A Zoidberg, Commented Jul 5, 2016 at 10:40
What are you uploading it into? That is, can you get the type information from the destination table? — Ben Thul
– Ben Thul, Commented Jul 5, 2016 at 12:59
I may need to create the table; otherwise one could just use the existing table parameters. — Dr. John A Zoidberg
– Dr. John A Zoidberg, Commented Jul 5, 2016 at 13:21
You'll have to write the inference algorithm yourself, e.g. read in each row and decide, for each column, if all the cells contain only numbers. There's no silver bullet inference algorithm because CSVs are not universal. — univerio
– univerio, Commented Jul 5, 2016 at 17:52

Sam Bourne · Accepted Answer · 2016-07-05 19:03:43Z

1

Depending on your data you may be able to get away with simply using ast.literal_eval.

import ast

assert isinstance(ast.literal_eval('1'), int)
assert isinstance(ast.literal_eval('1.0', float)
assert isinstance(ast.literal_eval('True', bool)
assert isinstance(ast.literal_eval('"foobar"', basestring)

You could also use something a bit more complex such as messytables which provides a type_guess method.

http://messytables.readthedocs.io/en/latest/

edited Jul 5, 2016 at 19:03

answered Jul 5, 2016 at 17:53

Sam Bourne

6506 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Infer column type SQLAlchemy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related