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.
Re: [sqlalchemy] LONGVARCHAR
Am 29.10.2010 17:08, schrieb sandro dentella: Hi, firefox uses sqlite to store bookmark info. The file is called places.sqlite and the schema has type LONGVARCHAR for some fields and LONG for another. Autoloading that with sqlalchemy maps that columns to NullType rather that to a String / Integer one. Same for LONG type I don't really know if LONGVARCHAR/LONG are correct types for sqlite but they are accepted and used: is it possible to make sqlalchemy autoload correctly? Or should we ask firefox guys to use different types? LONGVARCHAR/LONG are not really supported types for SQLite for sure and will be handled via affinity rules. According to the affinity rules used by SQLite the LONGVARCHAR columns should have affinity TEXT, so maybe SQLalchemy or the SQLite wrapper in use could have done something about it, LONG should have affinity NUMERIC. I would doubt this is an SQLAlchemy problem, sounds more like a problem with the underlying python dbapi interface that is too limited or not properly configured to return the right types or affinities. dbapi specifies that the cursor.description() method MUST return a type_code, but last time i looked sqlite3 from the python stdlib ignores the part about type_code being required to compare with the available DBAPI type_codes. If Firefox used normal SQLite type names like TEXT/BLOB and INTEGER for those fields it would just work with all SQLite tools, even if they get affinities wrong. So one should at least question the design decision. Michael -- 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 sqlalch...@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] Why my python script can use in the server but not from the client machine?
Ning schrieb:
Hi
I have written a script connect a table in the database. It works
perfectly when I was doing the testing in the sever.
But i am not able to connect from the client. The client login has the
same premission as the loginning account in the sever.
In [8]: engine = create_engine('''mssql://W1001252521\NINGSTORAGE/
WeatherDB?trus
ted_connection=yes''')
Wild guess. You use the trusted connection aka Kerberos/Windows
Authentification login and do not have the right permissions set on your
client machine to connect with the user.
If you have klist or kerbtray installed on your machine, try to see
which tickets you have.
Michael
--
Michael Schlenker
Software Engineer
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 sqlalch...@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] Re: app memory leak - execute() with SA 0.5.2, PG 8.3, Psycopg2
Allen Bierbaum schrieb: The python process. The number of objects seems to remain fairly controlled. But the amount of resident memory used by the python process does not decrease. I had expected that by calling gc.collect(2) python would reclaim any objects that could be freed and free all memory associated with them, thus decreasing the consumed memory. Maybe this is an invalid assumption. Do you know any way to ask python to shrink it's process size (ie. clear unused memory that has been freed but evidently not given back to the OS)? Thats an invalid assumption for most systems with pooling allocators. Its usually called a high-watermark allocator which assumes your process has a steady state of memory usage. Its usually not a problem, unless your running short of virtual address space/swap space because any decent OS will just page out the unused memory blocks. But you may be out of luck still if the layout of the pool prevents such e.g. if one object in every memory page is active the system cannot swap out anything and you need a harder working GC to readjust all references to really get completly free blocks. In addition you need the cooperation of the OS free() call, not all systems really give memory back, even if you free() it, some just keep it in a pool for the process. One typical pattern to get around this is to just fork a worker for the memory intensive stuff and let it quit when done so the memory gets returned to the system. Michael -- Michael Schlenker Software Engineer 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] COMMENT ON clause for schema.Column/schema.Table?
Hi, i looked at the API doc but found no obvious way to specify a COMMENT clause for a schema object. Is there any support for this in SA or anything planned? Basically you can add comments to schema objects in e.g. Oracle, via a COMMENT command. (for Oracle http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4009.htm#i2119719, SQL Server supports something similar, and Postgresql too http://www.postgresql.org/docs/8.3/interactive/sql-comment.html) Would be nice to be able to reflect a docstring into the database schema that way. Michael -- Michael Schlenker Software Engineer 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: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Adding text() at the end of an sql.expression?
Hi all, trying to figure out how i could add an arbitrary string to an sql.expression. If have something like this: s = select([columns]) if cond: s = s.where(text(cond)) if addtl: # how to tack addtl to the end of the statement? # this does not work s = s + text(addtl) Basically i have an legacy interface i need to base on sqlalchemy that takes a list of columns, a where clause and an 'additional' thing. Additional was used for things like 'order by', 'having' etc. but can be anything basically. e.g. columns = [test.c.foo, test.c.bar] cond = foo=20 addtl = some sql stuff and i want it to transform to: SELECT test.foo, test.bar FROM test WHERE foo=20 some sql stuff So i try to find a good way to tack such a SQL string at the end of a sqlexpression, but haven't seen it yet. Is there a way? Michael -- Michael Schlenker Software Engineer 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: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy 0.4.2b released
Jorge Godoy schrieb: Jon Rosebaugh wrote: What are we supposed to do with Unicode? As far as I can tell, the Unicode type passes its defined length directly to the underlying string, so that a Unicode(30) column is turned into a VARCHAR(30) or the dialect equivalent. I may be able to determine that a particular column should hold up to 30 Unicode characters, for example, but if the default encoding of UTF-8 is used, each character will use anywhere from one to four byes, so that in the extreme case, it will be trying to stuff a 120-byte string into a VARCHAR(30)! The only way to be certain of the length is to use UTF-32, although in practice I doubt I will ever get astral plane characters in this app, so UTF-16 would probably be sufficient. But I _like_ UTF-8 and would prefer to use it. Perhaps the Unicode type should multiply its length by 4? What RDBMS you use? And what is the encoding of the specific database where you tested this? If you have a database with UTF-8 encoding then it will be expecting 30 Unicode characters coded with the UTF-8 encoding, no matter how many bytes it takes to store then. A VARCHAR(30) is not referring to 30 bytes, but to 30 characters. Unless he is using Oracle with NLS_LENGTH_SEMANTICS as Bytes..., then its really Bytes..., but thats a bad setting for various reasons. Michael -- Michael Schlenker Software Engineer 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: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Oracle date/datetime oddities
Hi all,
I'm not sure if its a bug or an intended feature, but the default behaviour
of sqlalchemy when reading Oracle DATE fields is annoying. cx_Oracle
rightfully returns datetime.datetime objects, but Sqlalchemy truncates this
to datetime.date objects.
Why is it done like this (in lib/sqlalchemy/databases/oracle.py:34-60)?
Wouldn't it be a better choice to default to OracleDateTime instead of
OracleDate
for queries without bound metadata?
Its not a (major) problem when querying via a table object, where i can override
the column type with a sane version (OracleDateTime), but for queries directly
using conn.execute() its ugly.
Basically this throws up:
import sqlalchemy as sa
import datetime
engine = sa.create_engine('oracle://scott:[EMAIL PROTECTED]')
conn = engine.connect()
conn.execute('create table dtest (a DATE)')
# insert a row with date and time
now = datetime.datetime(2007,12,11,13,11,00)
conn.execute('insert into dtest values (:dt)', {'dt':now})
# check its there
rows = conn.execute('select a from dtest where a=:dt',{'dt':now})
for r in rows:
if rows[0]==now:
print Found
else:
print Not Found
This prints 'Not Found' even though the row is there and is returned correctly
by cx_Oracle.
I would expect to get at least identity for this.
So is this a bug and should i add a report or is it a 'feature' of some kind
and will
not change even if i report a bug?
Michael
--
Michael Schlenker
Software Engineer
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: [EMAIL PROTECTED]
Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Oracle date/datetime oddities
Michael Bayer schrieb: On Nov 12, 2007, at 11:16 AM, Michael Schlenker wrote: Hi all, your above test means to say if r[0]==now:, else you get a runtime error. Right, should copy and paste instead of retype... When I run it with that fix, the row matches and it prints Found. Sorry. Just retried with sqlalchemy SVN HEAD and there it works, with 0.4.0b6 it failed. So it looks much better that way. Michael --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] MetaData reflect on Oracle with multiple visible tables
Hi all, i try to introspect a schema for an Oracle database on a site with multiple users, which have nearly identical schemas (common scenario on a development host). E.g. in the database there are: msc_ora1.sct0001_00 msc_ora2.sct0001_00 create by the two users msc_ora1 and msc_ora2. Now it try to use SQLAlchemy (0.4 with cx_Oracle) to introspect those tables and get an exception: import sqlalchemy as sa meta = sa.MetaData engine = sa.create_engine(oracle://msc_ora1:[EMAIL PROTECTED]) conn = engine.connect() meta.bind = conn meta.reflect() Traceback (most recent call last): File stdin, line 1, in module File build\bdist.win32\egg\sqlalchemy\schema.py, line 1200, in reflect File build\bdist.win32\egg\sqlalchemy\schema.py, line 114, in __call__ File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 909, in reflectta ble File build\bdist.win32\egg\sqlalchemy\engine\base.py, line 1178, in reflectt able File build\bdist.win32\egg\sqlalchemy\databases\oracle.py, line 434, in refl ecttable File build\bdist.win32\egg\sqlalchemy\databases\oracle.py, line 380, in _res olve_table_owner File build\bdist.win32\egg\sqlalchemy\databases\oracle.py, line 368, in _loc ate_owner_row sqlalchemy.exceptions.AssertionError: There are multiple tables with name 'SCT00 0001_00' visible to the schema, you must specifiy owner I tried with: meta.reflect(schema=msc_ora1) but it raises the same exception. I have noticed the 'owner' attribute on the Table class, but how can i specify an owner for reflection as requested by the Exception? Michael -- Michael Schlenker Software Engineer 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: [EMAIL PROTECTED] Sitz der Gesellschaft: Bremen | Geschäftsführer: Karl Heinz Zachries 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MetaData reflect on Oracle with multiple visible tables
Michael Bayer schrieb:
On Nov 5, 2007, at 6:44 AM, Michael Schlenker wrote:
I tried with:
meta.reflect(schema=msc_ora1)
but it raises the same exception. I have noticed the 'owner'
attribute on the Table class,
but how can i specify an owner for reflection as requested by the
Exception?
you'd have to precreate those tables with the owner attribute:
Table('tablename', meta, owner='someowner', autoload=True)
Okay, found that workaround.
But the real fault is that database/oracle.py ignores the schema argument when
table_names() is being called and that
additionally no default schema name (which should be the USER) is set.
Filed ticket #847 for this, with a patch attached that fixes the issue.
There are other places in oracle.py with similar issues, after a cursory
browsing.
Michael
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
