BUG: Quote table names in to_sql

[the4thv]

xl.to_sql(con=conn, name='d1187b08-4943-4c8d-a7f6-6c06b7cb9509', flavor='mysql', if_exists='replace')
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "C:\Dev\Project\www\venv\project\lib\site-packages\pandas\core\generic.py", line 1165, in to_sql
    chunksize=chunksize, dtype=dtype)
  File "C:\Dev\Project\www\venv\project\lib\site-packages\pandas\io\sql.py", line 571, in to_sql
    chunksize=chunksize, dtype=dtype)
  File "C:\Dev\Project\www\venv\project\lib\site-packages\pandas\io\sql.py", line 1660, in to_sql
    dtype=dtype)
  File "C:\Dev\Project\www\venv\project\lib\site-packages\pandas\io\sql.py", line 1417, in __init__
    super(SQLiteTable, self).__init__(*args, **kwargs)
  File "C:\Dev\Project\www\venv\project\lib\site-packages\pandas\io\sql.py", line 669, in __init__
    self.table = self._create_table_setup()
  File "C:\Dev\Project\www\venv\project\lib\site-packages\pandas\io\sql.py", line 1478, in _create_table_setup
    ',\n  '.join(create_tbl_stmts) + "\n)"]
  File "C:\Dev\Project\www\venv\project\lib\site-packages\pandas\io\sql.py", line 1363, in _get_valid_mysql_name
    raise ValueError("Invalid MySQL identifier '%s'" % uname)
ValueError: Invalid MySQL identifier '`d1187b08-4943-4c8d-a7f6-6c06b7cb9509`'

Expected Output

(no output, just successful write to db table)

output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.4.3.final.0
python-bits: 32
OS: Windows
OS-release: 7
machine: AMD64
processor: Intel64 Family 6 Model 58 Stepping 9, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None

pandas: 0.18.1
nose: None
pip: 8.1.2
setuptools: 18.0.1
Cython: None
numpy: 1.11.0
scipy: None
statsmodels: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.4.2
pytz: 2015.4
blosc: None
bottleneck: None
tables: None
numexpr: None
matplotlib: None
openpyxl: None
xlrd: 0.9.3
xlwt: 0.8.0
xlsxwriter: 0.7.3
lxml: None
bs4: 4.4.0
html5lib: 0.999999
httplib2: 0.9.2
apiclient: 1.5.0
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
boto: 2.38.0
pandas_datareader: None

Proposed fix:

Line 1478 of pandas\io\sql.py, wrapping the escape(self.name) with ``:

create_stmts = ["CREATE TABLE " + escape(self.name) + " (\n" +
',\n '.join(create_tbl_stmts) + "\n)"]

[jorisvandenbossche]

@the4thv that's indeed probably a good fix, however, note that the usage of the mysql flavor is deprecated and will probably be removed in the next release (0.19.0, but the fix could still be included in 0.18.2, but maybe not worth).
You should use an sqlalchemy engine instead for writing to mysql tables. Can you try that and see if this does work using an sqlalchemy engine?

[the4thv]

I can getting it working with sqlalchemy, thanks. 👍

[jorisvandenbossche]

@the4thv I will leave this issue open as it is probably a problem for sqlite as well (and the support for this flavor without using sqlalchemy is to stay, only mysql will be removed)

PR with the fix is always welcome!

[the4thv]

@jorisvandenbossche, I will give it a go at lunchtime.

[sudheesh001]

It looks like this issue is already fixed and good to close.

pandas/pandas/io/sql.py

Line 1322 in 34bd15d

create_stmts = ["CREATE TABLE " + escape(self.name) + " (\n" +

[jorisvandenbossche]

I don't think the proposed change in the top post is implemented. But in any case, best to add a test for this to check that it is actually solved.

Do you have a small reproducible example that shows it is working?

PRs always welcome to add a test case!

[huguesv]

I've added a test case in the PR referenced above that has now been merged. Was there anything else to do in order to close this issue?

[TomAugspurger]

I believe so.

@jorisvandenbossche does that sound right? table names are quoted for sqlite, and everyone else should be using a sqlalchemy engine?

[jorisvandenbossche]

Yes!

[gafortiby]

This bug fix actually introduced a new bug for those of us using pandas with Netezza and jaydebeapi (which is treated like sqlite). What happens is that Netezza treats quotes as literal and injects these quotes into the table name, which then prevents other parts of the code from functioning properly.

EDIT: I found that if ALLCAPS are used for table names, it works fine even for Netezza and jaydebeapi.