[sqlalchemy] Re: select in
Michael Bayer ha scritto: On Feb 11, 2008, at 6:36 AM, Glauco wrote: Hi all, What's the simplest way for do a simple: select * from myTable where id in (1,2,3); I've solved this by using Subquery but final qry isn't pretty as this one. mytable.select(mytable.c.id.in_([1,2,3])) Thank you michael, i've lost the in_ operator. Glauco -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software® info(at)sferacarta.com Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054 ++ --~--~-~--~~~---~--~~ 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] invalid byte sequence for encoding:utf8 Postgresql
Hello, I had a postgresql database:
CREATE DATABASE panizzolosas
WITH OWNER = postgres
ENCODING = 'UTF8';
and i'm using sqlalchemy 0.4.2p3.
this is my code
self.metadata=MetaData()
engine = create_engine(stringaDATABASE, encoding='utf-8',
echo=False,convert_unicode=True)
self.metadata.bind= engine
try:
table_ditta=Table('tblditta', self.metadata, autoload=True)
mapper(Ditta, table_ditta)
except :
print Error
On the database I had some record with the caracter à and if I make
some updates I receive the error
ProgrammingError: (ProgrammingError) invalid byte sequence for
encoding UTF8: 0xe03537
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
client_encoding.
'UPDATE tblditta SET codice=%(codice)s WHERE tblditta.id = %
(tblditta_id)s' {'tblditta_id': 592, 'codice': 'Cibra Publicit
\xe0577'}
\xe0577 is à I suppose..
Any help would be appreciated.
Thanks..
Bye Emyr
--~--~-~--~~~---~--~~
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: schema changes
Michael Bayer wrote: What if they exist but don't match the spec that SA has created? just try it out...create_all() by default checks the system tables for the presence of a table first before attempting to create it Cool, (same with dropping). When would SA drop a table? this is controlled by a flag called checkfirst. This a parameter to the methods or does it need to be set in some config file? if you're concerned about people running your application against databases created from a different version and then failing, I would suggest adding a version table to your database which contains data corresponding against the version of your application in some way. Good plan. There has been interest among some SA users over building a generic schema comparison system and I think even some prototypes are available, though I think thats a fairly complicated and unreliable approach to take for this particular issue. Do the projects have a name? cheers, Chris -- Simplistix - Content Management, Zope Python Consulting - http://www.simplistix.co.uk --~--~-~--~~~---~--~~ 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] Executing sql text from session
Hi,
I'm reading sqlalchemy 0.4 documentation about how execute a sql text
from session obejct.
In this example,
Session = sessionmaker(bind=engine, transactional=True)
sess = Session()
result = sess.execute(select * from table where id=:id, {'id':7})
It's only present in 0.4.x versions ?
How can do it in 0.3.x versions?
TIA.
--~--~-~--~~~---~--~~
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: invalid byte sequence for encoding:utf8 Postgresql
On Feb 13, 2008, at 2:28 AM, [EMAIL PROTECTED] wrote:
Hello, I had a postgresql database:
CREATE DATABASE panizzolosas
WITH OWNER = postgres
ENCODING = 'UTF8';
and i'm using sqlalchemy 0.4.2p3.
this is my code
self.metadata=MetaData()
engine = create_engine(stringaDATABASE, encoding='utf-8',
echo=False,convert_unicode=True)
self.metadata.bind= engine
try:
table_ditta=Table('tblditta', self.metadata, autoload=True)
mapper(Ditta, table_ditta)
except :
print Error
On the database I had some record with the caracter à and if I make
some updates I receive the error
ProgrammingError: (ProgrammingError) invalid byte sequence for
encoding UTF8: 0xe03537
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
client_encoding.
'UPDATE tblditta SET codice=%(codice)s WHERE tblditta.id = %
(tblditta_id)s' {'tblditta_id': 592, 'codice': 'Cibra Publicit
\xe0577'}
\xe0577 is à I suppose..
would need to see the code youre using to insert data. Also, set
assert_unicode=True on your create_engine() call; that will
illustrate non unicode strings being passed into the dialect. When
using convert_unicode=True at the engine level, *all* strings must be
python unicode strings, i.e. u'somestring'.
--~--~-~--~~~---~--~~
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: Executing sql text from session
On Feb 13, 2008, at 9:04 AM, maxi wrote:
Hi,
I'm reading sqlalchemy 0.4 documentation about how execute a sql text
from session obejct.
In this example,
Session = sessionmaker(bind=engine, transactional=True)
sess = Session()
result = sess.execute(select * from table where id=:id, {'id':7})
It's only present in 0.4.x versions ?
How can do it in 0.3.x versions?
the feature is present in 0.3 as well but you have to say
sess.execute(mapper, select * from table..., {id:7}), where
mapper is optional (can be None).
--~--~-~--~~~---~--~~
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: Selecting a column of a Secondary table
this is the association object pattern, as described at:
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_relation_patterns_association
with that pattern, you should specifically *not* use the secondary
argument on your mapper as the group_user_tbl will be explicitly mapped.
After setting that up you may want to look at the association proxy
which can smooth some of the clumsiness of dealing with an association
object: http://www.sqlalchemy.org/docs/04/plugins.html#plugins_associationproxy
On Feb 13, 2008, at 9:38 AM, g wrote:
Say we have a many-to-many relation:
mapper(Group, group_tbl,
properties={'users' : relation(User, secondary=group_user_tbl) }
)
group_user_tbl = Table('group_user', metadata,
Column('group_id', Integer, ForeignKey('group.group_id')),
Column('user_id', Integer, ForeignKey('user.user_id')),
Column('expiry', DateTime)
)
Is there any way to make the 'expiry' an attribute to the User of a
particular Group?
eg.
my_group = session.query(Group).get(30)
u = my_group.users[0]
exp = u.expiry
exp = datetime.datetime.now()
session.flush()
--~--~-~--~~~---~--~~
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] Selecting a column of a Secondary table
Say we have a many-to-many relation:
mapper(Group, group_tbl,
properties={'users' : relation(User, secondary=group_user_tbl) }
)
group_user_tbl = Table('group_user', metadata,
Column('group_id', Integer, ForeignKey('group.group_id')),
Column('user_id', Integer, ForeignKey('user.user_id')),
Column('expiry', DateTime)
)
Is there any way to make the 'expiry' an attribute to the User of a
particular Group?
eg.
my_group = session.query(Group).get(30)
u = my_group.users[0]
exp = u.expiry
exp = datetime.datetime.now()
session.flush()
--~--~-~--~~~---~--~~
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: select in
Heh, I have the opposite problem, I now find myself typing in_ and == in interactive query editors On Feb 13, 2008 3:40 AM, Glauco [EMAIL PROTECTED] wrote: Michael Bayer ha scritto: On Feb 11, 2008, at 6:36 AM, Glauco wrote: Hi all, What's the simplest way for do a simple: select * from myTable where id in (1,2,3); I've solved this by using Subquery but final qry isn't pretty as this one. mytable.select(mytable.c.id.in_([1,2,3])) Thank you michael, i've lost the in_ operator. Glauco -- ++ Glauco Uri glauco(at)sferacarta.com Sfera Carta Software(R) info(at)sferacarta.com Via Bazzanese,69 Casalecchio di Reno(BO) - Tel. 051591054 ++ --~--~-~--~~~---~--~~ 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] executing stored procedure which returns rows
I'm using and loving SQLAlchemy 0.4.3dev_r4136 but I am having a problem with (drum roll) a legacy database schema. I'm using pyodbc on Unix. The primary keys in a legacy table are alphanumeric and must be generated by a MSSQL stored procedure which returns a single result row with a single unnamed char(12). How can I execute this procedure with SQLAlchemy? I tried the obvious session.execute(EXEC sp_new_foo_key).fetchone() and variations with text() and select() but I always get this error: class 'pyodbc.ProgrammingError': Attempt to use a closed cursor. The typemap argument to text() looked like it might do what I need, but I don't see how to use it with the unnamed result column, and I'm not sure it would actually result in the correct execution method. I gather this is because the mssql dialect implements returns_rows_text() as a regexp matching just SELECT and sp_columns. SQL Server Profiler shows the statement being executed from SQLAlchemy as an RPC instead of a SQL batch like when I do the EXEC from SQL Server Management Studio. --~--~-~--~~~---~--~~ 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: pymssql delete problem
Thanks for your continuing interest in my silly problem It's not a silly problem, it's a important fundamental operation that ought to work correctly! Try the attached patch against pymssql 0.8.0. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- pymssql.py.patch Description: Binary data
[sqlalchemy] Re: executing stored procedure which returns rows
I'm using pyodbc on Unix. blink You are??? This statement jumped out of the message for me. Can you please describe your setup to the list? There is a lot of interest in this configuration. --~--~-~--~~~---~--~~ 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: executing stored procedure which returns rows
On Feb 13, 2008, at 12:03 PM, John Keith Hohm wrote: I'm using and loving SQLAlchemy 0.4.3dev_r4136 but I am having a problem with (drum roll) a legacy database schema. I'm using pyodbc on Unix. The primary keys in a legacy table are alphanumeric and must be generated by a MSSQL stored procedure which returns a single result row with a single unnamed char(12). How can I execute this procedure with SQLAlchemy? I tried the obvious session.execute(EXEC sp_new_foo_key).fetchone() and variations with text() and select() but I always get this error: class 'pyodbc.ProgrammingError': Attempt to use a closed cursor. The typemap argument to text() looked like it might do what I need, but I don't see how to use it with the unnamed result column, and I'm not sure it would actually result in the correct execution method. I gather this is because the mssql dialect implements returns_rows_text() as a regexp matching just SELECT and sp_columns. SQL Server Profiler shows the statement being executed from SQLAlchemy as an RPC instead of a SQL batch like when I do the EXEC from SQL Server Management Studio. that's the issue. we grep for SELECT-like statements in order to determine if we can close the cursor immediately. I think adding EXEC to the ms_is_select would fix this. As far as RPC vs. SQL batch that's determined by the DBAPI - we call cursor.execute() as opposed to callproc(), if thats significant. --~--~-~--~~~---~--~~ 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: schema changes
On Wednesday 13 February 2008 22:06:54 Don Dwiggins wrote: [EMAIL PROTECTED] wrote: we've put such a notion in our db, so the db knows what model-version it matches. Then, at start, depending on the versions one can decide which migration script to execute (if the db should be made to match the py-model), or which feautures to drop (if py-model should follow the db). btw there should be some tricky strategy as of what to call db-model-version and when that version really changes. This is getting into a big area: the problem of version control/configuration management for databases its not any bigger than any other configuration management of something structured that is deployed in the field... as long it consists of pieces and these pieces can go/combine wrong... .. (Just having a realized object-level schema should be a big step forward.) mmm this is going slightly offtopic, but there might be many levels of modelling (db-model - mapping - obj-model - concept-model - behavioural-model - ... - philosophy-model if-u-want). The more the merrier ;-) i.e. the easier to change something without affecting something else on a far-away-level -- but is harder to grasp, takes longer to develop, and needs more initial investment. But on a long run pays off very well - of course IF there is no long run, no point doing it. e.g. Right now i have a bitemporal machine applied even over some part of the code of the system, organised as sort of read-only db/repository of replaceable pieces of code. And changes of piece in this repository are treated same as changes in the salary of someone - maybe less dynamic but still changes... adieu svilen --~--~-~--~~~---~--~~ 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: executing stored procedure which returns rows
I'm using pyodbc on Unix. blink You are??? This statement jumped out of the message for me. Can you please describe your setup to the list? There is a lot of interest in this configuration. I am using unixodbc-2.2.11 as packaged by Ubuntu 7.10 (gutsy) with locally-installed freetds-0.64 (the tdsodbc Ubuntu package was 0.63 and had issues with SQL Server 2005) and locally-installed pyodbc-2.0.52. I configured the server with tds version = 8.0 in /etc/ freetds/freetds.conf, defined the FreeTDS driver in /etc/odbcinst.ini, and the DSN in /etc/odbc.ini; it works fine once I figured out the URL needs to look like mssql://myuser:mypass@/mydbname?dsn=DSN_FROM_ODBCINI (note the slash after the at symbol). I know the documentation suggests this is not reliable, but I haven't had any problems that I would attribute to pyodbc. --~--~-~--~~~---~--~~ 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: executing stored procedure which returns rows
John, I am using unixodbc-2.2.11 as packaged by Ubuntu 7.10 (gutsy) with That sounds very promising, I have been meaning to have a go at this for a while. Can you do me a favor and run the unit tests using your current setup? Run alltests.py and append text_as_varchar=1 to the dburi (a few mssql tests rely on this). Save the stdout and stderr and send them to me. This would really help us gauge how much work on unix support is needed. For comparison, a run on windows with pyodbc has about 40 test failures. Paul --~--~-~--~~~---~--~~ 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: schema changes
[EMAIL PROTECTED] wrote: This is getting into a big area: the problem of version control/configuration management for databases its not any bigger than any other configuration management of something structured that is deployed in the field... as long it consists of pieces and these pieces can go/combine wrong... I found it bigger because I couldn't find any ready-made tools for DB versioning (let along diff'ing), and that in a DB, there's a mix of structural elements and data elements that need to be sorted out (it's kind of like doing a version upgrade on a running program without disturbing the program's state 8^). If I've missed something, and this problem has been well and completely solved, I'd be delighted to hear of it. -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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] DB2 driver coming
...surprised I didn't see it here first, but: http://antoniocangiano.com/2008/02/13/ibm-releases-db2-adapter-for-sqlalchemy/ --~--~-~--~~~---~--~~ 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: How are Oracle IDs generated?
On Feb 13, 8:03 pm, Richard Jones [EMAIL PROTECTED] wrote:
I've tried poking through the documentation and source to determine
this, but it's still unclear to me how SQLAlchemy generates IDs for
new rows in Oracle.
There's support for sequences in the oracle backend, but there don't
appear to be sequences created for my tables.
It is a two step process.
- define the sequences in Oracle
- indicate to SA that you want to use it for a given table
For example:
from sqlalchemy import *
meta = MetaData()
roles = Table('box_role',
meta,
Column('id', Integer, Sequence('seq_box_role_id'),
primary_key=True),
Column('name', String(50), nullable=False),
)
According to:
http://www.sqlalchemy.org/docs/04/metadata.html#metadata_defaults_sequences
the first step may not be required but if your DBA has some naming
conventions
he wants you to follow making them by hand may be a good idea.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
