Problem writing to SQL db with DataFrame column names as numbers

[klonuo]

Example:

import numpy as np
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mssql+pyodbc://localhost\\sqlexpress/test')

x1 = pd.DataFrame(np.random.rand(5,3))
x2 = pd.DataFrame(np.random.rand(5,3), columns=['0', '1', '2'])

x1.to_sql('x1', con=engine)
x2.to_sql('x2', con=engine)

which creates these two tables with same column names (numbers as string) but different contents:

x1:

index   0   1   2
0   NULL    NULL    NULL
1   NULL    NULL    NULL
2   NULL    NULL    NULL
3   NULL    NULL    NULL
4   NULL    NULL    NULL

x2:

index   0   1   2
0   0.749669142411769   0.120300003868206   0.424437055115581
1   0.205079144551286   0.977461912401449   0.480845560482874
2   0.635489979788438   0.0149051603743131  0.469838520671857
3   0.765853984144486   0.927480038225488   0.665683952437241
4   0.0688135748993546  0.990064095395479   0.921328636417753

So x1 table and x2 table have same column names, and as expected I can change the values in x1 with sql query, but pandas seems to failed in instructing sqlalchemy to write the data in x1 columns.

[jorisvandenbossche]

Can you show pd.show_versions()? I thought this was already fixed and tested for.

[klonuo]

Is this supposed to be fixed in 0.14.1?

INSTALLED VERSIONS
------------------
commit: None
python: 2.7.8.final.0
python-bits: 64
OS: Windows
OS-release: 8
machine: AMD64
processor: Intel64 Family 6 Model 15 Stepping 11, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None

pandas: 0.14.0
nose: 1.3.0
Cython: 0.20.2
numpy: 1.8.0
scipy: 0.13.3
statsmodels: 0.5.0
IPython: 2.1.0
sphinx: 1.2.1
patsy: 0.2.1
scikits.timeseries: None
dateutil: 2.2
pytz: 2013.9
bottleneck: 0.8.0
tables: 3.1.1
numexpr: 2.3.1
matplotlib: 1.3.1
openpyxl: 1.8.3
xlrd: 0.9.2
xlwt: 0.7.5
xlsxwriter: None
lxml: 3.3.1
bs4: None
html5lib: 0.999
bq: None
apiclient: None
rpy2: None
sqlalchemy: 0.9.4
pymysql: None
psycopg2: 2.5.3 (dt dec pq3 ext)

[jorisvandenbossche]

If I look at the commit history, that should already have been fixed in 0.14 (6c36769, from #6902).
However, there was a further fix for older sqlalchemy versions that is only in 0.14.1 (#7456). You have a recent sqlalchemy, but maybe the same issue appears for mssql ? (we have no tests for that)

Could you try with 0.14.1 ?

[klonuo]

I thought this was already fixed and tested for.

This is indeed fixed in 0.14.1

[jorisvandenbossche]

OK, anyway, thanks for reporting and testing!