Columns are typed "REAL" if they are integers with some NaN/blanks

[simonw]

This is bad. If a column has all integers and some blanks it should result in an INTEGER.

Example: this CSV https://github.com/openelections/openelections-data-ca/blob/master/2016/20161108__ca__general__yolo__precinct.csv produces this SQL table:

CREATE TABLE "2016/20161108__ca__general__yolo__precinct" (
"county" TEXT,
  "precinct" INTEGER,
  "office" INTEGER,
  "district" REAL,
  "party" REAL,
  "candidate" INTEGER,
  "votes" INTEGER
,
FOREIGN KEY (county) REFERENCES county(id),
    FOREIGN KEY (party) REFERENCES party(id),
    FOREIGN KEY (precinct) REFERENCES precinct(id),
    FOREIGN KEY (office) REFERENCES office(id),
    FOREIGN KEY (candidate) REFERENCES candidate(id))

[simonw]

https://stackoverflow.com/questions/34383000/pandas-to-sql-all-columns-as-nvarchar/34384664 may be useful here

[rgieseke]

I ran into the same problem, in my https://github.com/rgieseke/pandas-datapackage-reader reader I convert columns with missing integers to dtype object, but that doesn't help with writing to sql.

The Tableschema library (https://github.com/frictionlessdata/tableschema-py) might be helpful here for inferring data types:

test.csv

a,b
1,0.1
2,0.2
,0.3
4,

from tableschema import infer

infer("test.csv")

{
  'fields': [
    {'format': 'default', 'name': 'a', 'type': 'integer'},
    {'format': 'default', 'name': 'b', 'type': 'number'}
  ],
   'missingValues': ['']
}

[simonw]

Fixed in a8ab524 and 0997b7b

[simonw]

@rgieseke your pandas-datapackage-reader tool looks fascinating, I'll definitely have a play with that.