pd.read_sql_query doesn't find existing table(s)

Code Sample

from tqdm import tqdm
import sqlite3
import pandas as pd

def fins(tickers, dbname):
    
    db = sqlite3.connect(f'dbname')
    
    dataset = []
    
    tables = ['_balance_sheet', '_cash_flow', '_income_statement']
    
    for i in tqdm(tickers): 
        for j in tqdm(tables):
            SQL = f'SELECT * FROM {i}{j}'
            print(SQL)
            pd.read_sql_query(SQL, db)

# executing the function throws an error
fins(tickers, 'financials.db')

Problem Description

SQL tables are not recognized in nested for loop. Similar to #9262. Query works fine when passed directly in the function. The code snippet is not reproducible as I'm working on my own database

Expected Output

A list of datasets for each ticker selected, like shown below.

Error Message

A DatabaseError is thrown, although the dataset exists, as shown in the picture above.

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
~/.local/share/virtualenvs/pynance-C6oRUGpU/lib/python3.7/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1377             else:
-> 1378                 cur.execute(*args)
   1379             return cur

OperationalError: no such table: NLY_balance_sheet

During handling of the above exception, another exception occurred:

DatabaseError                             Traceback (most recent call last)
<ipython-input-34-1ccd252d2593> in <module>()
----> 1 fins(tickers, 'financials.db')

<ipython-input-33-22a9339f800f> in fins(tickers, dbname)
     11             SQL = f'SELECT * FROM {i}{j}'
     12             print(SQL)
---> 13             pd.read_sql_query(SQL, db)

~/.local/share/virtualenvs/pynance-C6oRUGpU/lib/python3.7/site-packages/pandas/io/sql.py in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize)
    312     return pandas_sql.read_query(
    313         sql, index_col=index_col, params=params, coerce_float=coerce_float,
--> 314         parse_dates=parse_dates, chunksize=chunksize)
    315 
    316 

~/.local/share/virtualenvs/pynance-C6oRUGpU/lib/python3.7/site-packages/pandas/io/sql.py in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize)
   1411 
   1412         args = _convert_params(sql, params)
-> 1413         cursor = self.execute(*args)
   1414         columns = [col_desc[0] for col_desc in cursor.description]
   1415 

~/.local/share/virtualenvs/pynance-C6oRUGpU/lib/python3.7/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1388             ex = DatabaseError(
   1389                 "Execution failed on sql '%s': %s" % (args[0], exc))
-> 1390             raise_with_traceback(ex)
   1391 
   1392     @staticmethod

~/.local/share/virtualenvs/pynance-C6oRUGpU/lib/python3.7/site-packages/pandas/compat/__init__.py in raise_with_traceback(exc, traceback)
    402         if traceback == Ellipsis:
    403             _, _, traceback = sys.exc_info()
--> 404         raise exc.with_traceback(traceback)
    405 else:
    406     # this version of raise is a syntax error in Python 3

~/.local/share/virtualenvs/pynance-C6oRUGpU/lib/python3.7/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1376                 cur.execute(*args, **kwargs)
   1377             else:
-> 1378                 cur.execute(*args)
   1379             return cur
   1380         except Exception as exc:

DatabaseError: Execution failed on sql 'SELECT * FROM NLY_balance_sheet': no such table: NLY_balance_sheet

INSTALLED VERSIONS ------------------ commit: None python: 3.7.0.final.0 python-bits: 64 OS: Darwin OS-release: 17.4.0 machine: x86_64 processor: i386 byteorder: little LC_ALL: en_US.UTF-8 LANG: en_US.UTF-8 LOCALE: en_US.UTF-8

pandas: 0.23.3
pytest: None
pip: 10.0.1
setuptools: 39.2.0
Cython: None
numpy: 1.15.0
scipy: 1.1.0
pyarrow: None
xarray: None
IPython: 6.4.0
sphinx: None
patsy: None
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 2.2.2
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: 4.2.3
bs4: 4.6.0
html5lib: 1.0.1
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: 0.5.0
pandas_datareader: 0.6.0

[WillAyd]

I am not able to reproduce this as the below works after creating a small table called foo locally - can you try and craft a more minimal and reproducible example?

In [11]: db = sqlite3.connect('test_db')
In [12]: pd.read_sql_query('SELECT * from foo', db)
Out[12]: 
Empty DataFrame
Columns: [id]
Index: []

In [14]: for _ in range(1):
    ...:     for tbl in ('foo',):
    ...:         print(pd.read_sql_query(f'SELECT * from {tbl}', db))
    ...:         
    ...:     
Empty DataFrame
Columns: [id]
Index: []

INSTALLED VERSIONS

commit: 776fed3
python: 3.7.0.final.0
python-bits: 64
OS: Darwin
OS-release: 17.7.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.24.0.dev0+418.g776fed3ab
pytest: None
pip: 18.0
setuptools: 40.0.0
Cython: 0.28.5
numpy: 1.15.0
scipy: None
pyarrow: None
xarray: None
IPython: 6.5.0
sphinx: None
patsy: None
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

Hi @WillAyd thank you for the reply.

I'm attaching a zip file of the dataset I'm trying to query.

tickers = ['NLY', 'SYF', 'TPX']

def fins(tickers, dbname):
    '''retrieve dataset of financial statements'''

    db = sqlite3.connect(f'dbname')
    dataset = []
    tables = ['_balance_sheet', '_cash_flow', '_income_statement']
    
    for i in tickers: 
        for j in tables:
            SQL = f'SELECT * FROM {i}{j}'
            print(SQL)
            pd.read_sql_query(SQL, db)

# error happens when you call this
fins(tickers, 'financials.db')

When I run the function on this data, it says that the table(s) don't exist. I've been squinting at this for a bit so maybe I'm missing something obvious.

financials.db.zip

[WillAyd]

Hmm still unable to replicate:

In [8]:     for i in tickers: 
   ...:         for j in tables:
   ...:             SQL = f'SELECT * FROM {i}{j}'
   ...:             print(SQL)
   ...:             print(pd.read_sql_query(SQL, db).loc[0, 'title'])
   ...:         
   ...:         
SELECT * FROM NLY_balance_sheet
Assets
SELECT * FROM NLY_cash_flow
Cash Flows From Operating Activities
SELECT * FROM NLY_income_statement
Revenue
SELECT * FROM SYF_balance_sheet
Assets
SELECT * FROM SYF_cash_flow
Cash Flows From Operating Activities
SELECT * FROM SYF_income_statement
Revenue
SELECT * FROM TPX_balance_sheet
Assets
SELECT * FROM TPX_cash_flow
Cash Flows From Operating Activities
SELECT * FROM TPX_income_statement
Revenue

Tried on Python 3.6 and 3.7 so no difference there. Can you try on master and see if that resolves for you?

INSTALLED VERSIONS

commit: 2156431
python: 3.7.0.final.0
python-bits: 64
OS: Darwin
OS-release: 17.7.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.24.0.dev0+422.g2156431c3
pytest: None
pip: 10.0.1
setuptools: 39.2.0
Cython: 0.28.5
numpy: 1.15.0
scipy: None
pyarrow: None
xarray: None
IPython: 6.5.0
sphinx: None
patsy: None
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

That's strange :/

Then it must be something that does weird stuff in the background of my sessions. That'll teach me to use Jupyter Notebooks. I'll close this then. Thank you for your help.