Problems handling column names containing spaces or - #8
Description
Irrrespective of whether using column names containing a space or - character is good practice, SQLite does allow it, but sqlite-utils throws an error in the following cases:
from sqlite_utils import Database
dbname = 'test.db'
DB = Database(sqlite3.connect(dbname))
import pandas as pd
df = pd.DataFrame({'col1':range(3), 'col2':range(3)})
#Convert pandas dataframe to appropriate list/dict format
DB['test1'].insert_all( df.to_dict(orient='records') )
#Works fineHowever:
df = pd.DataFrame({'col 1':range(3), 'col2':range(3)})
DB['test1'].insert_all(df.to_dict(orient='records'))throws:
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
<ipython-input-27-070b758f4f92> in <module>()
1 import pandas as pd
2 df = pd.DataFrame({'col 1':range(3), 'col2':range(3)})
----> 3 DB['test1'].insert_all(df.to_dict(orient='records'))
/usr/local/lib/python3.7/site-packages/sqlite_utils/db.py in insert_all(self, records, pk, foreign_keys, upsert, batch_size, column_order)
327 jsonify_if_needed(record.get(key, None)) for key in all_columns
328 )
--> 329 result = self.db.conn.execute(sql, values)
330 self.db.conn.commit()
331 self.last_id = result.lastrowid
OperationalError: near "1": syntax error
and:
df = pd.DataFrame({'col-1':range(3), 'col2':range(3)})
DB['test1'].upsert_all(df.to_dict(orient='records'))results in:
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
<ipython-input-28-654523549d20> in <module>()
1 import pandas as pd
2 df = pd.DataFrame({'col-1':range(3), 'col2':range(3)})
----> 3 DB['test1'].insert_all(df.to_dict(orient='records'))
/usr/local/lib/python3.7/site-packages/sqlite_utils/db.py in insert_all(self, records, pk, foreign_keys, upsert, batch_size, column_order)
327 jsonify_if_needed(record.get(key, None)) for key in all_columns
328 )
--> 329 result = self.db.conn.execute(sql, values)
330 self.db.conn.commit()
331 self.last_id = result.lastrowid
OperationalError: near "-": syntax error