Skip to content

BUG: Quote table names in to_sql #13206

Closed
@the4thv

Description

@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)"]

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions