Re: [sqlalchemy] mssql and specifying a schema name?
Am 30.05.2012 20:03, schrieb Michael Bayer:
the default schema name is determined by:
SELECT default_schema_name FROM sys.database_principals WHERE name =
(SELECT user_name()) AND type = 'S'
for some reason on your system it's coming up as MyDatabase. You'd
want to fix that so that it comes up with dbo.
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...).
See for example
http://dba.stackexchange.com/questions/8318/sql-2008-r2-creates-user-schema-when-windows-user-creates-tables
for some other instance of this happening.
Michael
On May 30, 2012, at 1:52 PM, Lukasz Szybalski wrote:
Hello, I'm trying to autolaod my table image but it keeps
complaining that the table doesn't exists.
I've enabled the echo = true and I see that you specify in the
query:
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] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
ORDER BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine: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] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
ORDER BY [COLUMNS_1].[ORDINAL_POSITION] 2012-05-30 12:39:06,193
INFO sqlalchemy.engine.base.Engine ('image', 'MyDatabase' )
But my schema name is dbo?
Where do I specify that? On create_engine? or?
import sqlalchemy
e =
sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker Session =
sessionmaker(bind=e, autoflush=True, autocommit=False) session =
Session()
from sqlalchemy.orm import mapper
#--- image_table = sqlalchemy.Table('image', metadata,
autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
Thanks, Lucas
-- You received this message because you are subscribed to the
Google Groups sqlalchemy group. To post to this group, send email
to sqlalchemy@googlegroups.com. To unsubscribe from this group,
send email to sqlalchemy+unsubscr...@googlegroups.com. For more
options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
Michael Schlenker
Software Architect
CONTACT Software GmbH Tel.: +49 (421) 20153-80
Wiener Straße 1-3 Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/ E-Mail: m...@contact.de
Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] mssql and specifying a schema name?
Hello,
I'm trying to autolaod my table image but it keeps complaining that the
table doesn't exists.
I've enabled the echo = true and I see that you specify in the query:
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] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine: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] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image',
'MyDatabase'
)
But my schema name is dbo?
Where do I specify that?
On create_engine?
or?
import sqlalchemy
e = sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
from sqlalchemy.orm import mapper
#---
image_table = sqlalchemy.Table('image', metadata, autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] mssql and specifying a schema name?
the default schema name is determined by:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = (SELECT user_name())
AND type = 'S'
for some reason on your system it's coming up as MyDatabase. You'd want to
fix that so that it comes up with dbo.
On May 30, 2012, at 1:52 PM, Lukasz Szybalski wrote:
Hello,
I'm trying to autolaod my table image but it keeps complaining that the
table doesn't exists.
I've enabled the echo = true and I see that you specify in the query:
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] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine: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] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image',
'MyDatabase'
)
But my schema name is dbo?
Where do I specify that?
On create_engine?
or?
import sqlalchemy
e = sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
from sqlalchemy.orm import mapper
#---
image_table = sqlalchemy.Table('image', metadata, autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
