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!