ImportError: Using URI string without sqlalchemy installed.

[jpryda]

Having created a new virtual environment and having installed SQLAlchemy and Pandas, I'm having trouble with the read_sql_query function which gives me the following error:

ImportError: Using URI string without sqlalchemy installed.

The code looks something like this:

URI_CONN_STRING = 'postgres://user:pass@ip-address:5432/db-name'
df = pd.read_sql_query(sql_query, URI_CONN_STRING, index_col=None, coerce_float=True, params=None, chunksize=None)

Expected Output

I'd expect to get a data frame with the results of the SQL query

Output of pd.show_versions()

## INSTALLED VERSIONS

commit: None
python: 2.7.10.final.0
python-bits: 64
OS: Darwin
OS-release: 15.2.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_GB.UTF-8
LOCALE: None.None

pandas: 0.19.0
nose: None
pip: 8.1.2
setuptools: 28.6.1
Cython: None
numpy: 1.11.2
scipy: None
statsmodels: None
xarray: None
IPython: 5.1.0
sphinx: None
patsy: None
dateutil: 2.5.3
pytz: 2016.7
blosc: None
bottleneck: None
tables: None
numexpr: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: 1.1.2
pymysql: None
psycopg2: None
jinja2: 2.8
boto: None
pandas_datareader: None

[jorisvandenbossche]

@jpryda Are you sure that you are executing this in the environment where you installed sqlalchemy? Can you show the interactive output where you can import sqlalchemy and the error traceback of the above code?

I tried to replicate this, but for me it works as expected.

[jpryda]

I've tried it on two different macs - one inside and one outside of a virtualenv. I can import sqlalchemy alone just fine. Here's the output - error at [4]:

In [1]: from datetime import datetime, timedelta
   ...: import pandas as pd
   ...: URI_CONN_STRING = 'postgres://user:pass@ip-address:5432/db-name
   ...: '
   ...: now = datetime.now()
   ...:

In [2]: end_date = now
   ...: num_days_back = 25
   ...: start_date = now.replace(hour=0, minute=0, second=0, microsecond=0) + timedelta(days=-num_day
   ...: s_back)
   ...: print start_date, end_date
   ...: 
2016-10-02 00:00:00 2016-10-27 12:45:08.963266

In [3]: sql_query = """select q1.name, q1.last_name, coalesce(q2.count, 0) as article_count, q3.ED, q
   ...: 3.email from
   ...:             (select schools.id, schools.name, users.first_name, users.last_name from schools 
   ...: left join community_managers_schools cms on schools.id=cms.school_id left join users on cms.u
   ...: ser_id=users.id where closed_at is null and purpose=1) as q1
   ...:         left join
   ...:                 (select auth.school_id, count(auth.school_id) from articles art left join aut
   ...: horships auth on art.id=auth.article_id where published is not null and published>='{}' and p
   ...: ublished<'{}' group by auth.school_id) as q2
   ...:         on q1.id=q2.school_id
   ...:         left join
   ...:             (select concat_ws(' ',users.first_name::text, users.last_name::text) as ED, schoo
   ...: l_id, users.email from users where leadership_position=3) as q3
   ...:         on q3.school_id=q1.id
   ...:         order by article_count desc""".format(start_date.strftime('%Y-%m-%d'), end_date.strft
   ...: ime('%Y-%m-%d %H:%M:%S'))

In [4]: df = pd.read_sql_query(sql_query, URI_CONN_STRING, index_col=None, coerce_float=True, params=
   ...: None, chunksize=None)

ImportError                               Traceback (most recent call last)
<ipython-input-4-864289c2e8a7> in <module>()
----> 1 df = pd.read_sql_query(sql_query, URI_CONN_STRING, index_col=None, coerce_float=True, params=None, chunksize=None)

/Users/xxx/Envs/sam-py/lib/python2.7/site-packages/pandas/io/sql.pyc in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize)
    326 
    327     """
--> 328     pandas_sql = pandasSQL_builder(con)
    329     return pandas_sql.read_query(
    330         sql, index_col=index_col, params=params, coerce_float=coerce_float,

/Users/xxx/Envs/sam-py/lib/python2.7/site-packages/pandas/io/sql.pyc in pandasSQL_builder(con, flavor, schema, meta, is_cursor)
    530         return SQLDatabase(con, schema=schema, meta=meta)
    531     elif isinstance(con, string_types):
--> 532         raise ImportError("Using URI string without sqlalchemy installed.")
    533     else:
    534         return SQLiteDatabase(con, is_cursor=is_cursor)

ImportError: Using URI string without sqlalchemy installed.

In [5]: import sqlalchemy

In [6]: import sys

In [7]: print sys.executable
/Users/xxx/Envs/sam-py/bin/python2.7

In [8]: 

and to show the file paths of the imported modules

In [1]: import sqlalchemy

In [2]: sqlalchemy.__file__
Out[2]: '/Users/xxx/Envs/sam-py/lib/python2.7/site-packages/sqlalchemy/__init__.pyc'

In [3]: import pandas

In [4]: pandas.__file__
Out[4]: '/Users/xxx/Envs/sam-py/lib/python2.7/site-packages/pandas/__init__.pyc'

[jorisvandenbossche]

Aha, is it possible you do not have psycopg2 (or another postgres driver) installed? When doing sqlalchemy.create_engine(URI_CONN_STRING) under the hood, this catches an ImportError and then gives the message about sqlalchemy not being installed. But, if you do not have the appropriate driver installed, this also gives an ImportError leading to the same (but misleading) error in read_sql

[jpryda]

Aha perfect thank you!

[jorisvandenbossche]

I am going to leave it open, as we should fix the error message for such cases