Change Schema For Sql File (drop Quotes In Names)

This question is similar to drastega's question I have similar problem, however I want to get rid of any quoting characters from names. Here is an example: CREATE TABLE Resolved (

Solution 1:

The code from Joris worked well in my case by just changing the line c.quote = False to c.name.quote = False with a pandas version 0.23.4, sqlalchemy=1.2.13 and python 3.6 for a postgres database.

Solution 2:

It is a bit strange that an sqlite application errors on the quotes (as sqlite should be case insensitive, quotes or not), and I would also be very cautious with some of the special characters you mention in column names. But if you need to insert the data into sqlite with a schema without quotes, you can do the following.

Starting from this:

import pandas as pd
from pandas.io import sql
import sqlalchemy
from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:')
df = pd.DataFrame({'Col1': [1, 2], 'Col2': [0.1, 0.2]})
df.to_sql('test', engine, if_exists='replace', index=False)

So by default sqlalchemy uses quotes (because you have capitals, otherwise no quotes would be used):

In [8]: res = engine.execute("SELECT * FROM sqlite_master;").fetchall()

In [9]: print res[0][4]
CREATETABLE test (
        "Col1" BIGINT,
        "Col2" FLOAT
)

Slqalchemy has a quote parameter on each Column that you can set to False. To do this combined with the pandas function, we have to use a workaround (as pandas already creates the Columns with the default values):

db = sql.SQLDatabase(engine)
t = sql.SQLTable('test', db, frame=df, if_exists='replace', index=False)

for c in t.table.columns:
    c.quote = False

t.create()
t.insert()

The above is equivalent to the to_sql call, but with interacting with the created table object before writing to the database. Now you have no quotes:

In [15]: res = engine.execute("SELECT * FROM sqlite_master;").fetchall()

In [16]: print res[0][4]
CREATETABLE test (
        Col1 BIGINT,
        Col2 FLOAT
)

Solution 3:

You can try to use lower case for both table name and column names. Then SQLAlchemy won't quote the table name and column names.