On 6/11/14, 4:07 PM, Joris Van den Bossche wrote:
> Hi,
>
> Since version 0.14 (released two weeks ago), pandas uses sqlalchemy in
> the SQL reading and writing functions to support different database
> flavors. A user reported an issue with SQL server:
> https://github.com/pydata/pandas/issues/7422 (and question on SO:
> http://stackoverflow.com/questions/24126883/pandas-dataframe-to-sql-function-if-exists-parameter-not-working).
>
> The user has set the default schema to `test`, but
> `engine.has_table('table_name')` and `meta.tables` still seem to
> return the tables in schema `dbo`.
> This leads to the following issue in our sql writing function `to_sql`:
> - when creating the table (using `Table.create()`), it creates it in
> the schema set as default (so 'test')
> - when checking for existence of the table (needed to see if the
> function has to fail, or has to append to the existing table), it
> however checks if the table exists in the 'dbo' schema
> - for this reason, the function thinks the table does not yet exists,
> tries to create it, resulting in a "|There is already an object named
> 'foobar' in the database|" error.
>
> Is there a way to resolve this? Is this an issue on our side, or
> possibly in sqlalchemy?
all sqlalchemy dialects make sure to determine what in fact is the
"default schema" upon first connect. With SQL server, turn on
echo='debug' and you will see this query:
2014-06-11 19:14:02,064 INFO sqlalchemy.engine.base.Engine
SELECT default_schema_name FROM
sys.database_principals
WHERE principal_id=database_principal_id()
2014-06-11 19:14:02,065 INFO sqlalchemy.engine.base.Engine ()
2014-06-11 19:14:02,065 DEBUG sqlalchemy.engine.base.Engine Col
('default_schema_name',)
2014-06-11 19:14:02,065 DEBUG sqlalchemy.engine.base.Engine Row ('dbo', )
that row ('dbo', ) you see is what is being determined as the "default"
schema. a subsequent has_table() command with no explicit schema will
use this value:
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT],
[COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND
[COLUMNS_1].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max))
2014-06-11 19:14:02,071 INFO sqlalchemy.engine.base.Engine ('foo', 'dbo')
so you want to look into the SQL server database_principal_id() function
and why that might not be working as expected. If you see that the
function is returning NULL or None, there's a workaround which is that
you can specify schema_name='xyz' to create_engine() as an argument;
however this value is only used if the above query is returning NULL
(which it should not be).
>
> BTW, I tried this myself with PostgreSQL, but couldn't reproduce it.
>
> Kind regards,
> Joris
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+unsubscr...@googlegroups.com
> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
> To post to this group, send email to sqlalchemy@googlegroups.com
> <mailto:sqlalchemy@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
