[sqlalchemy] Case Sensitivity at query time for SQL Server
Hi,
I'm trying to issue a query in SQL Server via select(cols, cond, ...) and I
would like to add collation at query execution time for executing the query
in a case sensitive mode.
I tried this:
cond = and_(tab.c.col3=="mystring1", tab.c.col2=="mystring2")
cond = cond.collate("SQL_Latin1_General_CP1_CS_AS")
q = select([tab.c.col3, tab.c.col4], cond, ...)
...
But that is raising a syntax error.
(SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Incorrect syntax near 'COLLATE'. (102); [42000] [SQL: u'SELECT
tab.col3, tab.col4 \nFROM tab \nWHERE (tab.col1 = ? AND tab.col2 = ?)
COLLATE SQL_Latin1_General_CP1_CS_AS'] [parameters: (u'mystring1',
u'mystring2')] (Background on this error at: http://sqlalche.me/e/f405)
Can anyone help me on how to achieve this?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/5db87be0-bb3e-400b-b1c6-6a49bdf20c7c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] PostgreSQL: problem with simple rowqcount query
HI everyone,
I'm trying to use sqlalchemy (0.9.10) to retrieve the number of rows of
table in PostgreSQL, here a stub of the code:
import sqlalchemy
from sqlalchemy import select, create_engine, MetaData, Table, Column
import datetime
engine =
create_engine('postgresql+pg8000://postgres:password@localhost/mydb')
metadata = MetaData(engine)
tab = Table('test', metadata,
Column('c1', sqlalchemy.INTEGER)
)
tab.create()
tab.insert().values(c1=123456).execute()
res = select([sqlalchemy.func.count('*')], None,
from_obj=[tab]).execute().fetchall()
print res
And here is the error I get:
Traceback (most recent call last):
File "C:\Users\Impara 01\Desktop\t.py", line 13, in
res = select([sqlalchemy.func.count('*')], None,
from_obj=[tab]).execute().fetchall()
File "C:\Python27\lib\site-packages\sqlalchemy\sql\base.py", line 386, in
execute
return e._execute_clauseelement(self, multiparams, params)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
1868, in _execute_clauseelement
return connection._execute_clauseelement(elem, multiparams, params)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 938,
in _execute_clauseelement
compiled_sql, distilled_params
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
1070, in _execute_context
context)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
1271, in _handle_dbapi_exception
exc_info
File "C:\Python27\lib\site-packages\sqlalchemy\util\compat.py", line 199,
in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
1063, in _execute_context
context)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line
442, in do_execute
cursor.execute(statement, parameters)
File "C:\Python27\lib\site-packages\pg8000\core.py", line 568, in execute
self._c.execute(self, operation, args)
File "C:\Python27\lib\site-packages\pg8000\core.py", line 1613, in execute
self.handle_messages(cursor)
File "C:\Python27\lib\site-packages\pg8000\core.py", line 1761, in
handle_messages
raise self.error
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('ERROR', '42P18',
'could not determine data type of parameter $1') u'SELECT count(%s) AS
count_1 \nFROM test' ('*',)
It seems that somehow the query is not formatted correctlyany hint?
Thanks in advance!
--
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.
Re: [sqlalchemy] sqlite problem with datetime format YYYY-MM-DDTHH:mm:ss
Thanks a lot!! On Thursday, September 3, 2015 at 5:18:29 PM UTC+2, Simon King wrote: > > That's fine - the "regexp" argument is only used when interpreting the > data that comes back from sqlite - it doesn't depend on you actually > creating the table from SQLAlchemy. > > It does require that you have an in-python "definition" of the table > structure, which I assume you already have, unless you are simply > using SQLAlchemy to issue raw SQL to sqlite. Do you have any table > definitions in your Python code, or are you using reflection? > > If you are using reflection, you can override specific column > definitions as described at > > > http://docs.sqlalchemy.org/en/rel_1_0/core/reflection.html#overriding-reflected-columns > > > and > > > http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/declarative/table_config.html#using-reflection-with-declarative > > > Simon > > On Thu, Sep 3, 2015 at 3:54 PM, Massi > > wrote: > > Hi Simone, > > > > thank you for your reply. Unfortunately I don't create the table so I > don't > > define the table, I only load it from the DB. I hope I intended your > > suggestion correctly. > > > > Thanks again. > > > > On Thursday, September 3, 2015 at 3:54:45 PM UTC+2, Simon King wrote: > >> > >> On Thu, Sep 3, 2015 at 1:05 PM, Massi wrote: > >> > Hi everyone, > >> > > >> > I'm trying to use sqlalchemy (0.9.10) to read a sqlite table which > >> > contains > >> > some datetime columns. As the title says these column are given in > the > >> > format -MM-DDTHH:mm:ss (I did not create the table). When I > execute > >> > the > >> > query, it succeeds, but when I try to convert the rows to lists I get > >> > the > >> > following error: > >> > > >> > ValueError: Couldn't parse datetime string: u'1957-09-04T23:04:37' > >> > > >> > If I try to create a similar table via sqlalchemy and then to read > the > >> > data > >> > everything works fine. The data in this case is stored in the format > >> > -MM-DD HH:mm:ss. > >> > Any hint? > >> > > >> > Thanks in advance! > >> > > >> > >> You could try passing the regexp parameter to the DateTime column in > >> your table definition: > >> > >> > >> > http://docs.sqlalchemy.org/en/rel_1_0/dialects/sqlite.html#sqlalchemy.dialects.sqlite.DATETIME > > >> > >> Hope that helps, > >> > >> Simon > > > > -- > > 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+...@googlegroups.com . > > To post to this group, send email to sqlal...@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.
Re: [sqlalchemy] sqlite problem with datetime format YYYY-MM-DDTHH:mm:ss
Hi Simone, thank you for your reply. Unfortunately I don't create the table so I don't define the table, I only load it from the DB. I hope I intended your suggestion correctly. Thanks again. On Thursday, September 3, 2015 at 3:54:45 PM UTC+2, Simon King wrote: > > On Thu, Sep 3, 2015 at 1:05 PM, Massi > > wrote: > > Hi everyone, > > > > I'm trying to use sqlalchemy (0.9.10) to read a sqlite table which > contains > > some datetime columns. As the title says these column are given in the > > format -MM-DDTHH:mm:ss (I did not create the table). When I execute > the > > query, it succeeds, but when I try to convert the rows to lists I get > the > > following error: > > > > ValueError: Couldn't parse datetime string: u'1957-09-04T23:04:37' > > > > If I try to create a similar table via sqlalchemy and then to read the > data > > everything works fine. The data in this case is stored in the format > > -MM-DD HH:mm:ss. > > Any hint? > > > > Thanks in advance! > > > > You could try passing the regexp parameter to the DateTime column in > your table definition: > > > http://docs.sqlalchemy.org/en/rel_1_0/dialects/sqlite.html#sqlalchemy.dialects.sqlite.DATETIME > > > Hope that helps, > > Simon > -- 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.
[sqlalchemy] sqlite problem with datetime format YYYY-MM-DDTHH:mm:ss
Hi everyone, I'm trying to use sqlalchemy (0.9.10) to read a sqlite table which contains some datetime columns. As the title says these column are given in the format -MM-DDTHH:mm:ss (I did not create the table). When I execute the query, it succeeds, but when I try to convert the rows to lists I get the following error: ValueError: Couldn't parse datetime string: u'1957-09-04T23:04:37' If I try to create a similar table via sqlalchemy and then to read the data everything works fine. The data in this case is stored in the format -MM-DD HH:mm:ss. Any hint? Thanks in advance! -- 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.
Re: [sqlalchemy] Error with SQL Server and utf-8 encoding
It works! Thanks a lot!
On Tuesday, September 1, 2015 at 5:42:08 PM UTC+2, Michael Bayer wrote:
>
>
>
> On 9/1/15 11:28 AM, Massi wrote:
>
> Hi everyone,
>
> I'm trying to manage read and write operations of utf-8 unicode strings
> with SQL Server (sqlalchemy 0.9.10), but I'm having some problems. I
> correctly write the strings to the database, but when I read them back and
> try to convert to unicode I get the following error:
>
> Traceback (most recent call last):
> File "C:\Users\Impara 01\Desktop\t.py", line 18, in
> print unicode(row[0], "utf-8")
> UnicodeDecodeError: 'utf8' codec can't decode byte 0xe0 in position 0:
> invalid continuation byte
> Process terminated with an exit code of 1
>
> Here is a sample code showing the problem:
>
> # -*- coding: utf-8 -*-
> import sqlalchemy
> from sqlalchemy import select, create_engine, MetaData, Table, Column
> import datetime
>
> engine =
> create_engine('mssql+pyodbc://MYHOST\SQLEXPRESS/user?trusted_connection=True&charset=utf8')
> metadata = MetaData(engine)
> t = Table('test', metadata,
> Column('unicode', sqlalchemy.dialects.mssql.VARCHAR())
> )
> t.create()
> s = "àèìòù"
> s = unicode(s, "utf-8")
> t.insert().values(unicode=s).execute()
> res = select([t.c.unicode]).execute().fetchall()
> for i, row in enumerate(res):
> print unicode(row[0], "utf-8")
>
> Can anyone point me out what I'm doing wrong?
>
>
> pyodbc and SQL Server are very particular about unicode. In this case it
> seems like you are passing a Python unicode literal into Pyodbc, and
> assuming Pyodbc knows how to handle that, but then on the reception side
> you're assuming that you're getting a bytestring back, and not again a
> Python Unicode object.
>
> to do a unicode round trip, use the SQLAlchemy Unicode type, and deal only
> with Python unicode literals in your script:
>
> t = Table( Column('x', sqlalchemy.Unicode()))
>
> s = u'àèìòù'
>
> t.insert().values(unicode=s) ...
>
> for row in res:
>print row[0]
>
> SQLAlchemy will make sure that the value is passed to pyodbc in the
> expected format, in this case it is likely encoding to utf-8 on the way in
> and decoding from utf-8 on the way out.
>
>
>
>
>
> Thanks in advance!
>
>
>
> --
> 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+...@googlegroups.com .
> To post to this group, send email to sqlal...@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.
[sqlalchemy] Error with SQL Server and utf-8 encoding
Hi everyone,
I'm trying to manage read and write operations of utf-8 unicode strings
with SQL Server (sqlalchemy 0.9.10), but I'm having some problems. I
correctly write the strings to the database, but when I read them back and
try to convert to unicode I get the following error:
Traceback (most recent call last):
File "C:\Users\Impara 01\Desktop\t.py", line 18, in
print unicode(row[0], "utf-8")
UnicodeDecodeError: 'utf8' codec can't decode byte 0xe0 in position 0:
invalid continuation byte
Process terminated with an exit code of 1
Here is a sample code showing the problem:
# -*- coding: utf-8 -*-
import sqlalchemy
from sqlalchemy import select, create_engine, MetaData, Table, Column
import datetime
engine =
create_engine('mssql+pyodbc://MYHOST\SQLEXPRESS/user?trusted_connection=True&charset=utf8')
metadata = MetaData(engine)
t = Table('test', metadata,
Column('unicode', sqlalchemy.dialects.mssql.VARCHAR())
)
t.create()
s = "àèìòù"
s = unicode(s, "utf-8")
t.insert().values(unicode=s).execute()
res = select([t.c.unicode]).execute().fetchall()
for i, row in enumerate(res):
print unicode(row[0], "utf-8")
Can anyone point me out what I'm doing wrong?
Thanks in advance!
--
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.
[sqlalchemy] MySql+oursql error on Time columns
Hi everyone,
I'm trying to use oursql module with sqlalchemy (0.9.10) for managing a
table with TIME columns, but I'm encountering an error, here a code snippet
showing the problem:
import sqlalchemy
from sqlalchemy import select, create_engine, MetaData, Table, Column
import datetime
engine = create_engine('mysql+oursql://root:password@localhost/test')
metadata = MetaData(engine)
t = Table('mysql_time', metadata,
Column('t1', sqlalchemy.dialects.mysql.TIME())
)
t.create()
t.insert().values(t1=datetime.time(8, 37, 35)).execute()
res = select([t.c.t1]).execute().fetchall()
for i, row in enumerate(res):
res[i] = list(row)
If you run the code, you will get the following error:
Traceback (most recent call last):
File "C:\Users\Impara 01\Desktop\t.py", line 15, in
res[i] = list(row)
File "C:\Python27\lib\site-packages\sqlalchemy\dialects\mysql\base.py",
line 1003, in process
microseconds = value.microseconds
AttributeError: 'datetime.time' object has no attribute 'microseconds'
Process terminated with an exit code of 1
If you run the code changing the engine creation to:
engine = create_engine('mysql://root:password@localhost/test')
everything's ok. Of course I NEED to use oursql.
Is it a bug? or am i missing something?
Thanks in advance!
--
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.
[sqlalchemy] SQLAlchemy and Linked Server (SQL Server)
Hi everyone, I'm trying to access data from a linked server using SQLalchemy (0.9.4), The usual connection established via pyodbc (engine = create_engine( 'mssql+pyodbc://scott:tiger@mydsn')) does not seem to work. Actually I can only read the data with SQL Server using SQL Management Studio and the openquery syntax. I'm totally new to the linked servers world, so what I'm looking for is a starting point to understand if SQLalchemy somehow allows to get access to the data. Any help or suggestion is welcome! Thanks in advance. -- 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.
Re: [sqlalchemy] Problem inserting records in blob column
Hi, I'm experiencing a similar problem in my program (Sqlalchemy 0.8.4).
In my case no blob column is involved, the problem seems to be related to
presence of a foreign key in the target table. Here is the traceback of the
error:
File "C:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 721,
in commit
self.transaction.commit()
File "C:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 354,
in commit
self._prepare_impl()
File "C:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line 334,
in _prepare_impl
self.session.flush()
File "C:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line
1818, in flush
self._flush(objects)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line
1936, in _flush
transaction.rollback(_capture_exception=True)
File "C:\Python27\lib\site-packages\sqlalchemy\util\langhelpers.py", line
58, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\session.py", line
1900, in _flush
flush_context.execute()
File "C:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py", line
372, in execute
rec.execute(self)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\unitofwork.py", line
525, in execute
uow
File "C:\Python27\lib\site-packages\sqlalchemy\orm\persistence.py", line
64, in save_obj
table, insert)
File "C:\Python27\lib\site-packages\sqlalchemy\orm\persistence.py", line
569, in _emit_insert_statements
execute(statement, params)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 662,
in execute
params)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 761,
in _execute_clauseelement
compiled_sql, distilled_params
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 828,
in _execute_context
None, None)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
1024, in _handle_dbapi_exception
exc_info
File "C:\Python27\lib\site-packages\sqlalchemy\util\compat.py", line 196,
in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 824,
in _execute_context
context = constructor(dialect, self, conn, *args)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line
446, in _init_compiled
self.__process_defaults()
File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line
821, in __process_defaults
val = self.get_insert_default(c)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line
777, in get_insert_default
return self._exec_default(column.default, column.type)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line
761, in _exec_default
return self.fire_sequence(default, type_)
StatementError: 'SQLiteExecutionContext' object has no attribute
'fire_sequence' (original cause: AttributeError: 'SQLiteExecutionContext'
object has no attribute 'fire_sequence') u'INSERT INTO dataset (set_id,
user_id, label, set_table, nam_table, val_table, datasource, info) VALUES
(?, ?, ?, ?, ?, ?, ?, ?)' [{'info': None, 'user_id': 1, 'val_table':
u'val_table', 'label': u'tab1', 'nam_tab': u'nam_table', 'set_table':
u'tab1', 'datasource': None}]
The construct which causes the error is this:
dataset.append_constraint(sa.ForeignKeyConstraint([dataset.c.set_id],
[task.c.set_id]))
If I remove this line of code everything goes well (but of course I cannot
set up the relationship between the dataset table and the task table).
Any help is really appreciated.
--
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/groups/opt_out.
[sqlalchemy] column_mapped_collection and insertion order preservation
Hi everyone,
in my script I'm using column_mapped_collection to create a collection
indexed by a column of a certain table. This is an example scenario (pseudo
code):
class Parent(object):
def __init__(self):
# define some fileds
def AddChild(self, new_child):
self.children[new_child.name] = new_child
class Child(object):
def __init__(self, name):
self.name = name
rel = relationship(Child,
collection_class=column_mapped_collection(child_tab.c.name))
mapper(Parent, parent_tab, properties={"children":rel})
mapper(Child, child_tab)
p = Parent()
p.AddChild(Child("child1"))
p.AddChild(Child("child2"))
session.add(p)
session.commit()
Everything works fine except for the fact that after the commit it
generally happens that the insertion order is not respected, that is the
child2 record is stored before child1. Is there a way to modify the code so
that the insertion order is preserved? I think this is somehow linked to
the use of Ordereddict but I have no idea about how to achieve it!
Any help is appreciated, thanks in advance!
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Sqlalchemy issue on Windows Server 2008 R2 sp1
Hi everyone, as the title says I'm trying to run sqlalchemy (pyodbc) with
SQL Server 2008 on Windows Server 2008 R2 sp1 but I'm failing with the
database connection. Here is a small script I'm using to test the
connection:
from sqlalchemy import *
from sqlalchemy.engine import reflection
url = "mssql+pyodbc://user:password@my.server.address/server_test"
e = create_engine(url)
insp = reflection.Inspector.from_engine(e)
print insp.get_table_names()
If I the script I get the following error:
Traceback (most recent call last):
File "connection.py", line 6, in
File "C:\Python27\lib\site-packages\sqlalchemy\engine\reflection.py",
line 118
, in from_engine
return Inspector(bind)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\reflection.py",
line 94,
in __init__
bind.connect().close()
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
2316, in
connect
return self._connection_cls(self, **kwargs)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 872,
in _
_init__
self.__connection = connection or engine.raw_connection()
File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line
2402, in
raw_connection
return self.pool.unique_connection()
File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 169, in
unique_c
onnection
return _ConnectionFairy(self).checkout()
File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 371, in
__init__
rec = self._connection_record = pool._do_get()
File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 697, in
_do_get
con = self._create_connection()
File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 174, in
_create_
connection
return _ConnectionRecord(self)
File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 256, in
__init__
self.connection = self.__connect()
File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 316, in
__connec
t
connection = self.__pool._creator()
File "C:\Python27\lib\site-packages\sqlalchemy\engine\strategies.py",
line 80,
in connect
return dialect.connect(*cargs, **cparams)
File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line
280, i
n connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (Error) ('08001', '[08001] [Microsoft][ODBC SQL
Serve
r Driver][DBNETLIB]Invalid connection. (14) (SQLDriverConnect); [01000]
[Microso
ft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Invalid Instance()).
(14)')
None None
The connection tested with SQL Server Management Studio works fine, so it
should not be an authentication problem.
Has anyone an idea of what I'm doing wrong?
Thanks in advance!
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/g0c-MFla6pAJ.
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] Re: Double quoted name column problem (Sqlite)
After a little search, I found that the problem is due to line 684 of the
file C:\Python27\Lib\site-packages\sqlalchemy\dialects\sqlite\base.py. The
column names of the table are processed with this regular expression
command:
name = re.sub(r'^\"|\"$', '', name)
which substitutes the first double quotes with a blank. Is this really
necessary? Does there exist any workaround to overcome this problem?
Il giorno martedì 18 settembre 2012 18:21:36 UTC+2, Massi ha scritto:
>
> Hi everyone, I'm using sqlalchemy 0.7.8 with sqlite and I'm encountering a
> problem trying to retrieve data from a table having a column named
> "input"_1. If I run this simple code:
>
> from sqlalchemy import *
>
> db = create_engine('sqlite:///test.db')
> db.echo = False
> metadata = MetaData(db)
> t = Table('my_table', metadata, autoload=True)
> print t.select().execute()
>
> i get the following error:
> OperationalError: (OperationalError) no such column: my_table.input"_1
> u'SELECT my_table.id, my_table.input"_1 FROM my_table'. It seems that the
> first double quotes are truncated by the query function. Of course I can
> arrange things such that no double quotes are present in column names, but
> I would like to know if SA can somehow handle this situation automatically
> and in a platform-independent way.
> Thanks in advance for your help!
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/0aCst9j8XcgJ.
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] Double quoted name column problem (Sqlite)
Hi everyone, I'm using sqlalchemy 0.7.8 with sqlite and I'm encountering a
problem trying to retrieve data from a table having a column named
"input"_1. If I run this simple code:
from sqlalchemy import *
db = create_engine('sqlite:///test.db')
db.echo = False
metadata = MetaData(db)
t = Table('my_table', metadata, autoload=True)
print t.select().execute()
i get the following error:
OperationalError: (OperationalError) no such column: my_table.input"_1
u'SELECT my_table.id, my_table.input"_1 FROM my_table'. It seems that the
first double quotes are truncated by the query function. Of course I can
arrange things such that no double quotes are present in column names, but
I would like to know if SA can somehow handle this situation automatically
and in a platform-independent way.
Thanks in advance for your help!
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/FtTj3V7BtcYJ.
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] Re: How to (quick) check if a table exists in schema?
Thank you for your reply Wichert, I already used the Inspector method get_table_names(), but using that I'd have to check if a table name is present in a vector which can have 100.000 elements. This can be even slower if done for lots of times. Maybe I can perform a binary search, but I'm not sure that the resulting vector is ordered. Am I wrong? Other Ideas? On 25 Apr, 16:11, Wichert Akkerman wrote: > On 04/25/2012 03:57 PM, Massi wrote: > > > > > > > > > > > Hi everyone, > > > in my script I have to deal with a huge database with thousands of > > tables. Given a table name (a python string) I would have to now if > > such a table exists or not. Up to now I have written this function: > > > def DBGetTableByName(table_name) : > > metadata = MetaData(engine) > > try : > > table = Table(table_name, metadata, autoload=True) > > return table > > except NoSuchTableError : > > return None > > > I use its return value to check if the table exists, but the problem > > is that it is too slow. Since I have to repeat this operation several > > times I wonder if there is a faster (and smarter) way to perform this > > control. > > Any hints? > > Use the inspector: > > from sqlalchemy.engine.reflection import Inspector > > inspector = Inspector.from_engine(engine) > print table_name in inspector.get_table_names() > > You can find the documentation > here:http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html?highlight=insp... > > Wichert. -- 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] How to (quick) check if a table exists in schema?
Hi everyone, in my script I have to deal with a huge database with thousands of tables. Given a table name (a python string) I would have to now if such a table exists or not. Up to now I have written this function: def DBGetTableByName(table_name) : metadata = MetaData(engine) try : table = Table(table_name, metadata, autoload=True) return table except NoSuchTableError : return None I use its return value to check if the table exists, but the problem is that it is too slow. Since I have to repeat this operation several times I wonder if there is a faster (and smarter) way to perform this control. Any hints? Thanks in advance! -- 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] Re: InsertFromSelect on SQL server
Mike, I tried to apply the new patch but it was rejected (working on
windows 7 and using patch from GnuWin32); it seems that the target
lines in you patch are misaligned with those in my SA source files. I
tried to make the changes by hand, but I could not get it working. I
still get the 'postfetch' error.
On 18 Ago, 19:34, Michael Bayer wrote:
> On Aug 18, 2011, at 1:19 PM, Massi wrote:
>
>
>
>
>
>
>
>
>
> > I got:
>
> > File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
> > \sqlalchemy\engine\base.py", line 2285, in execute
> > return connection.execute(statement, *multiparams, **params)
> > File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
> > \sqlalchemy\engine\base.py", line 1399, in execute
> > params)
> > File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
> > \sqlalchemy\engine\base.py", line 1532, in _execute_clauseelement
> > compiled_sql, distilled_params
> > File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
> > \sqlalchemy\engine\base.py", line 1599, in _execute_context
> > None, None)
> > File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
> > \sqlalchemy\engine\base.py", line 1595, in _execute_context
> > context = constructor(dialect, self, conn, *args)
> > File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
> > \sqlalchemy\engine\default.py", line 433, in _init_compiled
> > self.postfetch_cols = self.compiled.postfetch
> > StatementError: 'MSSQLCompiler' object has no attribute
> > 'postfetch' (original cause: AttributeError: 'MSSQLCompiler' object
> > has no attribute 'postfetch') 'INSERT INTO...'
>
> here's another patch that should default all that stuff appropriately:
>
> diff -r 113a7ed95335 lib/sqlalchemy/dialects/mssql/base.py
> --- a/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:03:30 2011 -0400
> +++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 13:31:16 2011 -0400
> @@ -655,7 +655,9 @@
> seq_column = tbl._autoincrement_column
> insert_has_sequence = seq_column is not None
>
> - if insert_has_sequence:
> + if getattr(self.compiled._mssql_requires_identity_insert, False):
> + self._enable_identity_insert = True
> + elif insert_has_sequence:
> self._enable_identity_insert = \
> seq_column.key in self.compiled_parameters[0]
> else:
> diff -r 113a7ed95335 lib/sqlalchemy/sql/compiler.py
> --- a/lib/sqlalchemy/sql/compiler.py Thu Aug 18 13:03:30 2011 -0400
> +++ b/lib/sqlalchemy/sql/compiler.py Thu Aug 18 13:31:16 2011 -0400
> @@ -198,6 +198,10 @@
> # driver/DB enforces this
> ansi_bind_rules = False
>
> + postfetch = ()
> + prefetch = ()
> + returning = ()
> +
> def __init__(self, dialect, statement, column_keys=None,
> inline=False, **kwargs):
> """Construct a new ``DefaultCompiler`` object.
> diff -r 113a7ed95335 lib/sqlalchemy/sql/expression.py
> --- a/lib/sqlalchemy/sql/expression.py Thu Aug 18 13:03:30 2011 -0400
> +++ b/lib/sqlalchemy/sql/expression.py Thu Aug 18 13:31:16 2011 -0400
> @@ -4792,6 +4792,8 @@
> Executable._execution_options.union({'autocommit': True})
> kwargs = util.immutabledict()
>
> + _returning = None
> +
> def _process_colparams(self, parameters):
> if isinstance(parameters, (list, tuple)):
> pp = {}
--
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] Re: InsertFromSelect on SQL server
I got:
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 2285, in execute
return connection.execute(statement, *multiparams, **params)
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 1399, in execute
params)
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 1599, in _execute_context
None, None)
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 1595, in _execute_context
context = constructor(dialect, self, conn, *args)
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\default.py", line 433, in _init_compiled
self.postfetch_cols = self.compiled.postfetch
StatementError: 'MSSQLCompiler' object has no attribute
'postfetch' (original cause: AttributeError: 'MSSQLCompiler' object
has no attribute 'postfetch') 'INSERT INTO...'
On 18 Ago, 19:08, Michael Bayer wrote:
> On Aug 18, 2011, at 1:06 PM, Massi wrote:
>
>
>
>
>
>
>
>
>
> > I changed the code to (SA 0.7.2 with pyodbc2.1.9):
>
> > class InsertFromSelect(Executable, ClauseElement) :
> > _execution_options =\
>
> > Executable._execution_options.union({'autocommit': True})
> > def __init__(self, table, select) :
> > self.table = table
> > self.select = select
>
> > @compiler.compiles(InsertFromSelect, "mssql")
> > def visit_insert_from_select(element, compiler, **kw) :
> > compiler.isinsert = True
> > compiler._mssql_requires_identity_insert = True
> > tab_name = compiler.process(element.table, asfrom=True)
> > q = "INSERT INTO %s (%s) %s;" % (tab_name,
> > ', '.join([compiler.process(c) for c in element.table.c]),
> > compiler.process(element.select))
> > return q
>
> > applied the patch and I got the following error:
>
> It's just checking if theres a RETURNING clause added, which your clause does
> not yet support. Add _returning = False to your construct for now.
>
>
>
>
>
>
>
>
>
> > File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
> > \sqlalchemy\engine\base.py", line 2285, in execute
> > return connection.execute(statement, *multiparams, **params)
> > File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
> > \sqlalchemy\engine\base.py", line 1399, in execute
> > params)
> > File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
> > \sqlalchemy\engine\base.py", line 1532, in _execute_clauseelement
> > compiled_sql, distilled_params
> > File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
> > \sqlalchemy\engine\base.py", line 1599, in _execute_context
> > None, None)
> > File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
> > \sqlalchemy\engine\base.py", line 1595, in _execute_context
> > context = constructor(dialect, self, conn, *args)
> > File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
> > \sqlalchemy\engine\default.py", line 418, in _init_compiled
> > self._is_explicit_returning = compiled.statement._returning
> > StatementError: 'InsertFromSelect' object has no attribute
> > '_returning' (original cause: AttributeError: 'InsertFromSelect'
> > object has no attribute '_returning') 'INSERT INTO...'
>
> > On 18 Ago, 18:34, Michael Bayer wrote:
> >> On Aug 18, 2011, at 12:24 PM, Massi wrote:
>
> >>> Hi everyone, I'm trying to implement an InsertFromSelect workaround to
> >>> handle the identity insert issue of SQL server. This is more or less
> >>> what I'm doing:
>
> >>> class InsertFromSelect(Executable, ClauseElement) :
> >>> def __init__(self, table, select) :
> >>> self.table = table
> >>> self.select = select
>
> >>> @compiler.compiles(InsertFromSelect, "mssql")
> >>> def visit_insert_from_select(element, compiler, **kw) :
> >>> tab_name = compiler.process(element.table, asfrom=True)
> >>> q = "SET IDENTITY_INSERT %s ON;" %(tab_name)
> >&g
[sqlalchemy] Re: InsertFromSelect on SQL server
I changed the code to (SA 0.7.2 with pyodbc2.1.9):
class InsertFromSelect(Executable, ClauseElement) :
_execution_options =\
Executable._execution_options.union({'autocommit': True})
def __init__(self, table, select) :
self.table = table
self.select = select
@compiler.compiles(InsertFromSelect, "mssql")
def visit_insert_from_select(element, compiler, **kw) :
compiler.isinsert = True
compiler._mssql_requires_identity_insert = True
tab_name = compiler.process(element.table, asfrom=True)
q = "INSERT INTO %s (%s) %s;" % (tab_name,
', '.join([compiler.process(c) for c in element.table.c]),
compiler.process(element.select))
return q
applied the patch and I got the following error:
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 2285, in execute
return connection.execute(statement, *multiparams, **params)
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 1399, in execute
params)
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 1532, in _execute_clauseelement
compiled_sql, distilled_params
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 1599, in _execute_context
None, None)
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\base.py", line 1595, in _execute_context
context = constructor(dialect, self, conn, *args)
File "C:\Python26\lib\site-packages\sqlalchemy-0.7.2-py2.6-win32.egg
\sqlalchemy\engine\default.py", line 418, in _init_compiled
self._is_explicit_returning = compiled.statement._returning
StatementError: 'InsertFromSelect' object has no attribute
'_returning' (original cause: AttributeError: 'InsertFromSelect'
object has noattribute '_returning') 'INSERT INTO...'
On 18 Ago, 18:34, Michael Bayer wrote:
> On Aug 18, 2011, at 12:24 PM, Massi wrote:
>
>
>
>
>
>
>
>
>
> > Hi everyone, I'm trying to implement an InsertFromSelect workaround to
> > handle the identity insert issue of SQL server. This is more or less
> > what I'm doing:
>
> > class InsertFromSelect(Executable, ClauseElement) :
> > def __init__(self, table, select) :
> > self.table = table
> > self.select = select
>
> > @compiler.compiles(InsertFromSelect, "mssql")
> > def visit_insert_from_select(element, compiler, **kw) :
> > tab_name = compiler.process(element.table, asfrom=True)
> > q = "SET IDENTITY_INSERT %s ON;" %(tab_name)
> > q += "INSERT INTO %s (%s) %s;" % (tab_name,
> > ', '.join([compiler.process(c) for c in element.table.c]),
> > compiler.process(element.select))
> > q += "SET IDENTITY_INSERT %s OFF;" %(tab_name)
> > return q
>
> it would be better to use the built in IDENTITY_INSERT facilities of the
> MSSQL dialect itself.
>
> Set the "isinsert" flag on your compiled object, and also set a new flag I
> can add called "_mssql_requires_identity_insert":
>
> @compiler.compiles(InsertFromSelect, "mssql")
> def visit_insert_from_select(element, compiler, **kw) :
> compiler.isinsert = True
> compiler._mssql_requires_identity_insert = True
>
> patch for testing:
>
> diff -r 44e239751af8 lib/sqlalchemy/dialects/mssql/base.py
> --- a/lib/sqlalchemy/dialects/mssql/base.py Wed Aug 17 14:55:21 2011 -0400
> +++ b/lib/sqlalchemy/dialects/mssql/base.py Thu Aug 18 12:32:22 2011 -0400
> @@ -655,7 +655,9 @@
> seq_column = tbl._autoincrement_column
> insert_has_sequence = seq_column is not None
>
> - if insert_has_sequence:
> + if getattr(self.compiled._mssql_requires_identity_insert, False):
> + self._enable_identity_insert = True
> + elif insert_has_sequence:
> self._enable_identity_insert = \
> seq_column.key in self.compiled_parameters[0]
> else:
>
> if this works for you I can commit it with a short test and it will be in
> 0.7.3.
>
>
>
> > insert_from_select = InsertFromSelect(new_table, old_table))
> > engine.execute(insert_from_select)
>
> easier issue here, your construct also needs "autocommit" enabled if you are
> going to use connectionless execution:
>
> class InsertFromSelect(...):
> _execution_options = \
> Executable._execution_options.union({'autocommit': True})
>
> or better yet just subclass UpdateBase instead of (Executable, ClauseElement).
>
> will add a doc
--
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] InsertFromSelect on SQL server
Hi everyone, I'm trying to implement an InsertFromSelect workaround to handle the identity insert issue of SQL server. This is more or less what I'm doing: class InsertFromSelect(Executable, ClauseElement) : def __init__(self, table, select) : self.table = table self.select = select @compiler.compiles(InsertFromSelect, "mssql") def visit_insert_from_select(element, compiler, **kw) : tab_name = compiler.process(element.table, asfrom=True) q = "SET IDENTITY_INSERT %s ON;" %(tab_name) q += "INSERT INTO %s (%s) %s;" % (tab_name, ', '.join([compiler.process(c) for c in element.table.c]), compiler.process(element.select)) q += "SET IDENTITY_INSERT %s OFF;" %(tab_name) return q insert_from_select = InsertFromSelect(new_table, old_table)) engine.execute(insert_from_select) The code is executed without error, but 'new_table' is not filled. Important: in my code I cannot use the session to execute the query. Can anyone point me out what I'm doing wrong? Thanks! -- 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] Simple select is really slow when executed via ORM
Hi everyone,
I'm doing some test to evaluate the performance of querying with
sqlalchemy via ORM. I wrote a simple script to measure the execution
time of a simple select query made on relatively small table (300 000
records, 6 columns) in sqlite. Here is the script:
from sqlalchemy import *
from sqlalchemy.orm import mapper, sessionmaker
from time import clock
engine = create_engine('sqlite:///test.db', echo=False)
metadata = MetaData()
table = Table('projects', metadata,
Column('id', Integer, primary_key=True),
Column('inp1', String(50)),
Column('inp2', String(50)),
Column('inp3', String(50)),
Column('inp4', String(50)),
Column('inp5', String(50)),
)
class Project(object) :
pass
mapper(Project, table)
metadata.create_all(engine)
t = []
for i in range(30) :
t.append({"inp1":str(i), "inp2":str(i), "inp3":str(i),
"inp4":str(i), "inp5":str(i)})
c = clock()
engine.execute(table.insert(), t)
print "Insert: "+str(clock()-c)
session = sessionmaker(bind=engine)()
c = clock()
res = engine.execute(table.select()).fetchall()
print "Sql query: "+str(clock()-c)
c = clock()
res = session.query(Project).all()
print "Session query: "+str(clock()-c)
On my PC (windows 7, 64-bit, intel i7 2.93 Ghz) this is the output:
Insert: 3.41080167807
Sql query: 1.26728367673
Session query: 19.6452334842
The execution time of the ORM query is about 20 times the SQL one, and
this is definitely discouraging. So I guess if I'm doing something
wrong or if there are some tricks when using ORM that I'm not
considering. Any help is really appreciated.
Thanks in advance!
--
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] Ho do I syncronize ORM objects when working with multiple sessions?
Hi everyone, in my script I work with two different sessions (say session1 and session2) bounded to the same database and, consequently, with ORM objects obtained with queries issued on them. It can occur that an object related to session1 change a value of the corresponding mapped table record and, on the other hand, there exist an object related to session2 mapped to the same table record. In this case the second object becomes misaligned with respect to the mapped table. So, my question is...which is the best approach to handle this situation? Can a session object somehow become aware that it must be synchronized/ refreshed? Thanks in advance for your help! -- 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] SQL Server weird sorting behaviour
Hi everyone, I'm using sqlalchemy 0.6.8 to interact with an sql server database via pyodbc. I'm getting in troubles using the 'order by' clause on a varchar column which include positive or negative integer values. When I try to get values from this column ordered in ascending mode I get: 1 -1 11 -11 111 and so on...while I expect the resutl to be something like: -1 -11 1 11 111 or even better: -11 -1 1 11 111 Is there any way to achieve (or at least workaround) that with SQLalchemy? Thanks in advance! -- 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] Strange Error: FlushError: Instance has a NULL identity key.
Hi everyone, I'm writing a script using sqlalchemy 0.66 and sqlite3.
In my script I'm trying to set up a one to one relation between two
tables via ORM. Here is a simplified code showing my situation:
from sqlalchemy import *
from sqlalchemy.orm import *
class Parent(object) :
def __init__(self, name) :
self.name = name
class Child(object) :
def __init__(self, name) :
self.name = name
engine = create_engine("sqlite:///test.db", echo=False)
metadata = MetaData(engine)
prn_tab = Table("parent", metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
sqlite_autoincrement=True)
prn_tab.create()
chl_tab = Table("child", metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
sqlite_autoincrement=True)
chl_tab.create()
fck = ForeignKeyConstraint
chl_tab.append_constraint(fck([chl_tab.c.id], [prn_tab.c.id]))
rel = relationship(Child, uselist=False, passive_deletes=True)
mapper(Parent, prn_tab, properties={"child":rel})
mapper(Child, chl_tab)
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
session.add(Child("Carl"))
session.commit()
if you run the above code you will see that the commit statement
generates the following error:
sqlalchemy.orm.exc.FlushError: Instance has a
NULL identity key. Check if this flush is occuring at an
inappropriate time, such as during a load operation.
I really cannot understand the reason why this error is raisedcan
anyone give a hint? Tahnks in advance!
--
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] Sqlalchemy+sqlite3 autoload problem
Hi everyone, I'm writing a script using sqlalchemy 0.66 and sqlite3.
I'm encountering a problem trying to create and load a table from two
different engines.
Here is an example script showing the problem:
from sqlalchemy import *
engine1 = create_engine("sqlite:///test.db", echo=False)
metadata1 = MetaData(engine1)
try :
table = Table("user", metadata1, autoload=True)
table.drop()
except :
print "Not found"
engine2 = create_engine("sqlite:///test.db", echo=False)
metadata2 = MetaData(engine2)
table = Table("user", metadata2,
Column('id', Integer, primary_key=True),
Column('name', String),
Column('password', String), sqlite_autoincrement=True)
table.create()
metadata1 = MetaData(engine1)
print Table("user", metadata2, autoload=True)
print Table("user", metadata1, autoload=True)
As you can see, I create the table 'user' from engine2 and then I try
to load it both from engine1 and engine2. The try-except part do some
clean up and it is aimed only to make the script repeatable.
If you run the code you'll see that the first print statement is
executed correctly, while the second one raises a NoSuchTableError
exception. It seems to be connected with some flushing issue, but I
don't know what I am doing wrong. Any suggestion?
Thanks in advance.
--
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] Simple problem with SQLite and Standard Deviation
Hi everyone, I'm using sqlalchemy 0.6.6 with SQLite...my problem is simple, is there a way to compute standard deviation in sqlalchemy with SQlite as a backend? I googled and found that native SQLite does not support any aggregate function like MySQL 'stdev', but there exist some extensions which could make it available. Does sqlalchemy support something similar? Or can anyone point me out some (even ugly) hack to work around the problem? Thanks in advance! -- 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] Sqlalchemy+Kinterbasdb installation error
Hi everyone, I'm trying to access a Firebird (version 2.5) database through Kinterbasdb under windows 7 with python 2.6. I'have download the file: kinterbasdb-3.3.0.win32-py2.6.msi which can be found at this link: http://www.firebirdsql.org/index.php?op=devel&sub=python, but when I try to install it I get the following errors: - Could not create: kinterbasdb-py2.6 - Could not set key value: python 2.6 kinterbas-3.3.0 - Could not set key value:"C:\Python26\removekinterbasdb.exe" - Could not set key value:"C:\Python26\kinterbasdb-wininst.log" I'm currently running python 2.6 win32 on a 64 bit CPU. The installed Firebird version is 64bit too. Maybe is it a compatibility issue? I also tried kinterbasdb-3.3.0.win-amd64-py2.6.msi, but it didn't work. Any suggestions? Thanks in advance. -- 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] SqlAlchemy+cx_oracle decimal point problem with stddev
Hi everyone, in my script (python 2.6, Oracle10g, cx_oracle 5.0.4,
sqlalchemy 0.6.5) I'm running the following simple query on one of my
tables:
table = Table("my_data_table", metadata, autoload=True)
col = getattr(table.c, "my_integer_col")
res = select(func.stdev(col)).execute().fetchone()
where "my_integer_col" obviously contains only int values.
I get the following error:
...
File "C:\Python26\lib\site-packages\sqlalchemy\engine\base.py", line
2445, in _fetchone_impl
return self.cursor.fetchone()
File "C:\Python26\lib\site-packages\sqlalchemy\dialects\oracle
\cx_oracle.py", line 496, in maybe_decimal
return int(value)
ValueError: invalid literal for int() with base 10:
'18,89258326656747167219869520430353668307'
I think this is related with the usage of the comma decimal point in
the results, but the error is raised by the cx_oracle module so I
cannot handle it. is this a bug or am I missing something?
Thanks in advance!
--
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] Clearing an already defined mapper
Hi everyone, in my script I have to deal with a table whose number of columns can change at runtime. Since I have no information about the structure I will have to handle at a given moment, I would need to change dynamically the mapping during the program. If I try to re-map the table I obviously get the following error: sqlalchemy.exc.ArgumentError: Class '' already has a primary mapper defined. Use non_primary=True to create a non primary Mapper. clear_mappers() will remove *all* current mappers from all classes. On the other hand clear_mappers removes all the mappers that has been defined on the classes; furthermore, as the documentation says: clear_mappers` is *not* for normal use, as there is literally no valid usage for it outside of very specific testing scenarios Can anyone give a hint to handle correctly this situation or point me out if there is a better approach to face it? Thanks in advance. -- 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] Problem updating mapped child class
Ok, the title doesn't say much, so I'll try to explain my problem with
an example:
I have two classes:
class A(object) :
pass
class B(object) :
pass
which are bounded by a relation:
a_tab = Table(...)
b_tab = Table(...)
b_tab.append_constraint(ForeignKeyConstraint([a_tab.c.id],
[b_tab.c.a_id]))
mapper(A, a_tab, properties={"rel": relationship(B)})
mapper(B, b_tab)
a_tab is already populated and has a lot of records (say 200 000),
while b_tab is empty.
So I load data from a_tab and populate b_tab this way:
a_data = self.session.query(A).all()
engine.execute(b_tab.insert(), [{"a_id":x.id} for x in a_data])
now, of course, a_data[0].rel is []
Is there a way (possibly a fast way) to update the data of the
relation 'rel' in the records of a_data?
Thanks in advance for your help!
--
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] Newbie problem with relational mapping
Hi everyone, I'm getting in troubles doing some experiments with
sqlalchemy (0.6.3) orm. Here are two code snippets to show the
problems I'm encountering.
## map_1.py
engine = create_engine("mysql://user:passw...@localhost/mydb")
metadata = MetaData(engine)
parent_table = Table('parent', metadata,
Column('id', Integer, primary_key=True))
child_table = Table('child', metadata,
Column('id', Integer, primary_key=True),
Column('parent_id', Integer, ForeignKey('parent.id')))
metadata.create_all(engine)
engine.execute(parent_table.insert(), [{'id':1}, {'id':2}, {'id':3},
{'id':4}])
engine.execute(child_table.insert(), [{'parent_id':1}, {'parent_id':
2}, {'parent_id':2}, {'parent_id':2}])
## map_2.py
engine = create_engine("mysql://user:passw...@localhost/mydb")
metadata = MetaData(engine)
parent_table = Table("parent", metadata, autoload=True)
child_table = Table("child", metadata, autoload=True)
class Parent(object):
pass
class Child(object):
pass
mapper(Parent, parent_table, properties={'children':
relationship(Child)})
mapper(Child, child_table)
Session = sessionmaker()
Session.configure(bind=engine)
sess = Session()
res = sess.query(Parent).all()
print res[0].children
Everything works fine for map_1.py, but when I run map_2.py I get the
following error:
sqlalchemy.exc.ArgumentError: Could not determine join condition
between parent/child tables on relationship Parent.children. Specify
a 'primaryjoin' expression. If this is a many-to-many relationship,
'secondaryjoin' is needed as well.
Process terminated with an exit code of 1
Can anyone point me out what I am doing wrong? Thanks in advance!
--
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] Making a copy of a table
Hi everyone, I'm getting in trouble in th attempt to create a simple copy (structure and data) of a table in my database (sql server). I need the SQLAlchemy equivalent of the query: SELECT * INTO newtable FROM table How can I achieve this? I found that tometadata probably could do the trick, but I cannot understand how to use it (I'm pretty new to SA). Can anyone help me? thanks in advance! -- 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] SQLAlchemy+pymssql unicode problem
Hi everyone,
I'm trying to write a small script to get all the table names from
SQLServer database (with pymssql). Up to now the script is really
simple:
engine = create_engine("mssql+pymssql://user:passw...@db_host/
db_name", encoding="cp1252")
metadata = MetaData(engine, reflect=True)
tabs = metadata.tables.keys()
print tabs
When I try to run this script I get the following error:
Traceback (most recent call last):
File "DBScript.py", line 12, in
metadata = MetaData(engine, reflect=True)
File "D:\Program Files\Python26\Lib\site-packages\sqlalchemy
\schema.py", line
1788, in __init__
self.reflect()
File "D:\Program Files\Python26\Lib\site-packages\sqlalchemy
\schema.py", line
1915, in reflect
Table(name, self, **reflect_opts)
File "D:\Program Files\Python26\Lib\site-packages\sqlalchemy
\schema.py", line
207, in __new__
table._init(name, metadata, *args, **kw)
File "D:\Program Files\Python26\Lib\site-packages\sqlalchemy
\schema.py", line
261, in _init
reflecttable(self, include_columns=include_columns)
File "D:\Program Files\Python26\Lib\site-packages\sqlalchemy\engine
\base.py",
line 1776, in reflecttable
self.dialect.reflecttable(conn, table, include_columns)
File "D:\Program Files\Python26\Lib\site-packages\sqlalchemy\engine
\default.py
", line 217, in reflecttable
return insp.reflecttable(table, include_columns)
File "D:\Program Files\Python26\lib\site-packages\sqlalchemy\engine
\reflection
.py", line 411, in reflecttable
raise exc.NoSuchTableError(table.name)
sqlalchemy.exc.NoSuchTableError
I found that this error is due to a table in my DB called "Attività",
so I believe that it is related to a unicode issue. Does anyone have
any idea about this error? Thanks in advance for your help!
--
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.
