read_sql gives SQLAlchemy warning 'Could not instantiate type ...'

[drastega]

Related SO question: http://stackoverflow.com/questions/24096330/sawarning-could-not-instantiate-type-class-sqlalchemy-sql-sqltypes-integer

When I run the code below

import pandas as pd
import sqlalchemy

dbname = "sqlite:////Users/leda/home/Magnetic_stars/SQLite/Magnetic_stars.sqlite"
engine = sqlalchemy.create_engine(dbname)
res = pd.io.sql.read_sql("SELECT * FROM Resolved;", engine)

I get

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/dialects/sqlite/base.py:860: SAWarning: Could not instantiate type <class 'sqlalchemy.sql.sqltypes.INTEGER'> with reflected arguments [u'4']; using no arguments.
  coltype = self._resolve_type_affinity(type_)
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/sqlalchemy/dialects/sqlite/base.py:860: SAWarning: Could not instantiate type <class 'sqlalchemy.sql.sqltypes.INTEGER'> with reflected arguments [u'2']; using no arguments.
  coltype = self._resolve_type_affinity(type_)

In [3]: pd.show_versions()

INSTALLED VERSIONS
------------------
commit: None
python: 2.7.5.final.0
python-bits: 64
OS: Darwin
OS-release: 13.2.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: ru_RU.UTF-8

pandas: 0.14.0
nose: 1.3.1
Cython: 0.20.1
numpy: 1.8.1
scipy: 0.11.0
statsmodels: None
IPython: 0.13.2
sphinx: None
patsy: None
scikits.timeseries: None
dateutil: 2.2
pytz: 2014.4
bottleneck: None
tables: None
numexpr: 2.4
matplotlib: 1.3.1
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
bq: None
apiclient: None
rpy2: 2.2.1
sqlalchemy: 0.9.4
pymysql: None
psycopg2: None

The schema of my SQLite table:

sqlite> .schema Resolved
CREATE TABLE `Resolved` (
`Name` TEXT NOT NULL COLLATE NOCASE,
`Count` INTEGER NOT NULL,
`Obs_Date` TEXT NOT NULL,
`Bessel_year` REAL,
`Filter` TEXT NOT NULL,
`Rho_mas` INTEGER,
`Sigma_rho` INTEGER,
`Theta` REAL,
`Sigma_theta` REAL,
`Dm` REAL,
`Sigma_dm` REAL,
`Orbit` INTEGER,
`Ref_star` TEXT COLLATE NOCASE,
`Comments` TEXT COLLATE NOCASE
);

P.S. Sorry if something is wrong. This is my first issue.

[jorisvandenbossche]

@drastega Thanks for the report!

Apart from the warning you see, is the table imported well?
Do you get the same if you use pd.read_sql_table('Resolved', engine)?
Do you have other tables in the sqlite database?
Do you get the same warning when reading another table from the database?

[drastega]

@jorisvandenbossche

My table is imported well.
I get the same (warnings and result) using pd.read_sql_table('Resolved', engine).
I have other tables in my sqlite database.
I get the same warning when reading another table from the database.

After upgrading pandas to 0.14.0 I get also

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/pandas/io/excel.py:626: UserWarning: Installed openpyxl is not supported at this time. Use >=1.6.1 and <2.0.0.
  .format(openpyxl_compat.start_ver, openpyxl_compat.stop_ver))

[jorisvandenbossche]

@drastega Thanks for the answers. The warning you get is already reported and fixed (will be released in 0.14, see #7284, you can always get rid of this warning for now by installing openpyxl < 2.0).

For the SAWarning, I suppose this has something to do with what is reported here (#6416 (comment)). When reading one table, a SQLAlchemy meta object is created, and this will read the table schemes of all your tables in the database. So probably one of your tables is issueing the warning.

Could you inspect the schemes of your other tables if there is something that could trigger this warning? Or try to read in the others and see when it fails?

[drastega]

@jorisvandenbossche
The problem was in my SQLite table southern_stars

 .schema  southern_stars
CREATE TABLE `southern_stars` (
  `Object ID` varchar(3) NOT NULL,
  `Object` varchar(13) NOT NULL,
  `RA` varchar(13) NOT NULL,
  `DEC` varchar(13) NOT NULL,
  `Epoch` varchar(5) NOT NULL default 'J2000',
  `Magnitude` float NOT NULL,
  `Filter` varchar(5) NOT NULL default '800',
  `Exposure time` varchar(4) NOT NULL default '0.02',
  `Number of exposures` int(4) NOT NULL default '2000',
  `Days from new moon` int(2) NOT NULL default '14',
  `Sky condition` varchar(6) NOT NULL default '"phot"',
  `Seeing` float NOT NULL default '1.5',
  `Comment` varchar(20) NOT NULL);

It was incorrectly created (using MySQL syntax) many years ago. After removing this table from my SQLite database everyting is working.

[jorisvandenbossche]

@drastega Could you test the following:

import pandas as pd
import sqlalchemy

dbname = "sqlite:////Users/leda/home/Magnetic_stars/SQLite/Magnetic_stars.sqlite"
engine = sqlalchemy.create_engine(dbname)

meta  = sqlalchemy.MetaData()
meta.reflect(engine, only=['Resolved'])

pdsql = pd.io.sql.PandasSQLAlchemy(engine, meta=meta)
pdsql.read_sql("SELECT * FROM Resolved;")

If this runs without the warning?

(not to say you should do it like this in your code, but if this works, we can fix it in out code)

[jorisvandenbossche]

@drastega Aha, good to hear! But still, can you test the code snippet in my previous comment? (with the problematic table still in your database).

[drastega]

@jorisvandenbossche your new code works without warnings even with my old southern_stars table. Thanks!

[jorisvandenbossche]

@drastega Thanks for testing, that confirms my assumption in #7396.

Then I am closing this issue in favor of #7396 (more general issue for this problem). Thanks for the report!