0

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 (
              [Name] TEXT,  
              [Count] INTEGER,  
              [Obs_Date] TEXT,  
              [Bessel_year] REAL,
              [Filter] TEXT,
              [Comments] TEXT
            );

changes to:

CREATE TABLE Resolved (
              Name TEXT,  
              Count INTEGER,  
              Obs_Date TEXT,  
              Bessel_year REAL,
              Filter TEXT,
              Comments TEXT
            );

Following the steps, from the link above I have managed to change "[" to quotes. However, I don't want to use any quoting characters. I tried to read documentation about sqlalchemy's metadata. I know that I need to use quote=False parameter. But I don't know where to call it. Thank you in advance for your answers.

Community
  • 1
  • 1
Lukasz
  • 269
  • 4
  • 12
  • What is the code that you are using? Do you use a sqlalchemy engine or a sqlite3 connection in `to_sql`? And what version of pandas? And lastly, is there a reason you want to get rid of this? – joris Jan 05 '15 at 20:35
  • I am using: - python 2.7.8 from Anaconda 2.1.0 (64 bit) - pandas 0.14.1 - sqlalchemy 0.9.7 Initially I have started with sqlite3 library. However, it was adding those square brackets to the names. I have followed drastega's question where he had similar problem (but he wanted to have quotes). I have assumed that it might be not that difficult to modify the answer for my purpose ( I was wrong ). So, I switched to sqlalchemy. I do not mind which library I will be using, as long as I can obtain same schema. – Lukasz Jan 06 '15 at 08:22
  • The main reason to remove any quoting characters is that other department ( I am part-time working with them ), has different schema. They pull selected channels, for data processing and we have noticed that my schema crashes their part of the code. Since they are very busy, it would be beneficial to modify files into their schema. Unfortunately, I do not have access to their code, so I can't fix it (probably simple strip method would do the trick). – Lukasz Jan 06 '15 at 08:23
  • So I suppose you will also not be able to use all lower case column names? It is the default of sqlalchemy (used by pandas) to quote column names that have capitals (or are keywords). – joris Jan 06 '15 at 23:13
  • I would like to keep lower and upper case letters. Also some special characters: - % - ( - ) - . <-- dot - / - + - ^ - * I have noticed that I can't keep degree character (it throws a programming error), so I had to remove it. – Lukasz Jan 07 '15 at 08:36

3 Answers3

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.

eliasmaxil
  • 530
  • 4
  • 13
0

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]
CREATE TABLE 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]
CREATE TABLE test (
        Col1 BIGINT,
        Col2 FLOAT
)
joris
  • 133,120
  • 36
  • 247
  • 202
  • Looks interesting. I have tried to replicate your code, however it complains that: AttributeError: 'module' object has no attribute 'SQLDatabase' // This happens in line: db = sql.SQLDatabase(engine). Also sql.SQLTable does not exist. Is there a chance that you have loaded sql in a different way? Or maybe it is not available in my version? (I have checked complete pandas.io.sql) – Lukasz Jan 08 '15 at 08:16
  • Ah, the names I showed are for pandas >= 0.15.0. In pandas 0.14 they are called `PandasSQLAlchemy` and `PandasSQLTable`. You can try with that, but I am not fully sure it will work axactly the same. – joris Jan 08 '15 at 09:07
  • c.quote = False gives attribute error: AttributeError: can't set attribute – Lukasz Jan 08 '15 at 11:03
  • I have tried to workaround this myself. What I have noticed t.create() gives following error: `OperationalError: (OperationalError) table test already exists u'\nCREATE TABLE test (\n\t"Col1" BIGINT, \n\t"Col2" FLOAT\n)\n\n' () ` – Lukasz Jan 09 '15 at 08:18
  • Ah, it seems that it has to do with the sqlalchemy version. For me, the above worked, but when I updated my sqlalchemy version, I got the same "can't set attribute" message as you. On your last comment, there were some internal changes in the sql classes in 0.15, so it is possible it has something to do with that. – joris Jan 09 '15 at 08:34
  • Another solution is to monkey patch the method that creates the `Table` object (https://github.com/pydata/pandas/blob/master/pandas/io/sql.py#L816), and just copy the existing function, but then add `quote=False` in the `Column(...)` call. – joris Jan 09 '15 at 08:36
0

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.

hui chen
  • 1,004
  • 14
  • 19