SQL: suppress warning for BIGINT with sqlite and sqlalchemy<0.8.2 (GH7433)
State: closed

From discussion here: https://github.com/pydata/pandas/pull/7634#issuecomment-48111148 
Due to switching from Integer to BigInteger (to support int64 on some database systems), reading a table from sqlite with integers leads to a warning when you have an slqalchemy version of below 0.8.2.

I know it is very very late and goes against all reservations of putting in new stuff just before a release, but after some more consideration, I think we should include this (or at least something that fixes it, and I think this does). @jreback, you said not to worry about it (it is just a warning), but the sqlalchemy release that fixes it is only just a year old and this is something most users will try as the first thing I think when using the sqlalchemy functions (writing/reading simple dataframe with some numbers with sqlite), so should not get a warning they don't understand.

I tested it locally with sqlalchemy 0.7.8 (on Windows), and on travis it is tested with 0.7.1 (the py2.6 build) and there also the warnings disappeared.

What do you think?


Labels:
name: Bug
name: Dtype Conversions
description: Unexpected or buggy dtype conversions
name: IO SQL
description: to_sql, read_sql, read_sql_query

Comments:
ok, by me (as its wrapped in a try except even if it didn't work at all, no harm (though looks ok).

just thinking, does this check happen at import? Should it only happen if an sql function is used? Or does it not matter that much?

maybe should happen as part of `_is_sqlalchemy_engine`? (as io/sql.py IS imported always)

That check is of course executed for every `read_sql` function call, but I suppose it does no harm that it is executed more than once.

you should just do:

```
_SQLALCHEMY_INSTALLED = None
def _is_sqlalchemy_engine(...):
    if _SQLALCHEMY_INSTALLED is None:
        # do the check and then set it to True/False
```

we do this for things like `numexpr, big query, pytables` libraries and such where the import is 'expensive' (but needs to be done at function call time)

@jreback Thanks. Something like this?

yep that looks good (minor comment).

you can even do weird version specific stuff , see here: https://github.com/jorisvandenbossche/pandas/blob/master/pandas/io/pytables.py#L238
e.g. conditionally setting a module level variable in some cases....

Hmm, it doesn't work because I assign to `_SQLALCHEMY_INSTALLED` (set it to true/false) inside the function, and so it only looks at the local function scope. 
Do you know another way to solve it other than wrapping it in a list as suggested [here](http://stackoverflow.com/questions/8934772/local-var-referenced-before-assignment)?

sorry, you have to do `global _SQLALCHEMY_INSTALLED` INSIDE the function (so it knows which scope to evvaluate in, see the link above)

this is the I should never use global variables in python! (actually these are module level, so ok)

@jorisvandenbossche we don't have any build that test for NO sql stuff at all, right? (not sure its necessary, but don't wan to have any import issues)

maybe remove sqlalchemy from one of the py3 builds? (eg the 3.3, as it is also tested on 3.4)

Should I add a specific test for it? Or is just having a build without it sufficient? Locally it is no problem in an env without sqlalchemy.

u can just delete from say 3.3 (from the requirements file, just the sqlalchemy)

@jorisvandenbossche this looks fine. I'll adjust the build and then merge this.

yep, that's fine

ok, good 2 go. I would say build/upload the docs. If you need to push a doc commit go ahead. I'll wait for your ok to push the release commits.