to_sql if_exists argument with SQL server and other schema

[aergener]

The if_exists argument of the to_sql function doesn't check all schema for the table while checking if it exists. Furthermore, it inserts to the default schema, causing somewhat contradictory behavior.

For example, while using SQL Server with my default schema set to test, to_sql inserts the table into test.table_name. However, trying this again, with if_exists='replace', to_sql finds no table of the name dbo.table_name, and then tries to create test.table_name, causing an error.

Details :

http://stackoverflow.com/questions/24126883/pandas-dataframe-to-sql-function-if-exists-parameter-not-working

[jorisvandenbossche]

@aergener How did you connect to SQL server (how do you set the default schema)?

It is a bit strange that when checking for the table it looks in dbo, but when writing it used the sepcified test schema.

[aergener]

@jorisvandenbossche I connected to SQL Server using sqlalchemy.create_engine. The default schema is a user setting within SQL Server itself (not specified while connecting in python).

[jorisvandenbossche]

Can you check engine.has_table('foobar')? (instead of the pd.io.sql.has_table('foobar', engine) which gave a false False).

[aergener]

When the table is in the test schema:

>>> engine.has_table('foobar')
Out[9]: False

When creating a table in SQL Server if a schema is not specifically designated, the table will be created in the user's default schema. Perhaps this issue could be fixed if pandas specified a schema for sqlalchemy to use that the user could potentially override?

[jorisvandenbossche]

I cannot reproduce this with postgresql (set the default schema to something else than the default 'public', but both the checking if the table exists or writing the data uses the same schema I set as default). So I am thinking it is possibly a bug in the sqlalchemy interface to SQL server (just guessing, I don't use SQL server). As engine.has_table('foobar') should look in the default schema I think.

[jorisvandenbossche]

@aergener I posted on the sqlalchemy mailing list: https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/Ktu06z9x97c Can you try what is said there?

So add echo='debug' to create_engine( ), then then look in the logs (those are printed) when you use the to_sql function. There should somewhere be a query to get the default schema name.

[aergener]

@jorisvandenbossche Cool, I'll test this out then get back to you.

As an aside, I was wondering if you have thought about adding better datatype support to pandas. Specifically, looking at pandas.io.sql.PandasSQLTable._sqlalchemy_type all strings in pandas end up as text fields in SQL. I'm not sure about other flavors, but in SQL Server working with text fields is a pain, so it would be nice to have something like string_repr option in to_sql. Do you have an email I can contact you at? I would like to help with this, as well as get involved with pandas development in general, but I'm not sure how I should go about it, and you seem like the SQL guy around here...

[aergener]

@jorisvandenbossche It looks like it's not picking up anything for the default_schema_name and defaulting to dbo. Notice how there's no row after 2014-06-12 20:56:15,461 DEBUG sqlalchemy.engine.base.Engine Col ('default_schema_name',)

2014-06-12 20:56:15,459 INFO sqlalchemy.engine.base.Engine SELECT user_name()
2014-06-12 20:56:15,459 INFO sqlalchemy.engine.base.Engine ()
2014-06-12 20:56:15,460 DEBUG sqlalchemy.engine.base.Engine Col ('',)
2014-06-12 20:56:15,460 DEBUG sqlalchemy.engine.base.Engine Row (u'aergener', )
2014-06-12 20:56:15,460 INFO sqlalchemy.engine.base.Engine 
            SELECT default_schema_name FROM
            sys.database_principals
            WHERE name = ?
            AND type = 'S'

2014-06-12 20:56:15,460 INFO sqlalchemy.engine.base.Engine (u'aergener',)
2014-06-12 20:56:15,461 DEBUG sqlalchemy.engine.base.Engine Col ('default_schema_name',)
2014-06-12 20:56:15,467 INFO sqlalchemy.engine.base.Engine SELECT [TABLES_1].[TABLE_NAME] 
FROM [INFORMATION_SCHEMA].[TABLES] AS [TABLES_1] 
WHERE [TABLES_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [TABLES_1].[TABLE_TYPE] = ? ORDER BY [TABLES_1].[TABLE_NAME]
2014-06-12 20:56:15,467 INFO sqlalchemy.engine.base.Engine (u'dbo', u'BASE TABLE')

[jorisvandenbossche]

@aergener Hmm I don't know SQL Server, so don't know how it works with users and schema's. Maybe you follow up further at the sqlalchemy mailing thread?

[aldanor]

Sounds related to #7441 -- @jorisvandenbossche, looks like we'll need to be able to pass schema/meta manually to read_sql / to_sql functions after all? :)

@aergener Try this?

engine = create_engine(..., echo='debug')
meta = MetaData()
meta.reflect(bind=engine, schema='test')

[jorisvandenbossche]

@aldanor It is certainly related, but not necesarily the same issue, as 1) this just works with postgresql (if you are a user with another default schema, it just reads and writes from/to that schema), and 2) engine.has_table() will still look in the wrong place.

I mean, having a schema argument might be a way to circumvent this issue here, but this case should actually work out of the box? (although I don't know SQL Server, so can't say for sure)

UPDATE: my second point can of course be solved with the engine.has_table( .. , schema=..) if we have a schema argument.

[aldanor]

@jorisvandenbossche Can't say much about this case, don't know much about SQL Server either. In Oracle, I had to set the schema equal to original table owner when calling MetaData#reflect for meta to be able to reflect anything.

Regarding the original question, found a discussion on schemas in sqlalchemy with SQL Server here: https://groups.google.com/forum/#!topic/sqlalchemy/VZQ_SLprNQA -- again, it boils down to passing schema manually when all else fails.

Looks like there's many ways "default schema" could fail, quoting the link above:

Default Schema Name can be empty if the user logs in via an AD Group.
(in fact a Group user cannot be assigned a default schema name before
SQL Server 2012...).