[sqlalchemy] Re: patch for mssql odbc
On Tue, Apr 8, 2008 at 7:02 PM, Rick Morrison [EMAIL PROTECTED] wrote: I'll reply here rather than on the ticket as I'm unable to stay logged into Trac from here (dual TCP/IP address problem). I see no objection as long as we can document it somewhere to say it will impact the AutoTranslate argument on the odbc connection... Florent. --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
On Nov 25, 2007 6:24 PM, Paul Johnston [EMAIL PROTECTED] wrote: Hi Florent, Just realised we'd gone quiet on this thread... humm What bothers me is that I already get this comportement when running my query program from a Linux host (using pyodbc same version) but need the above mentioned patch on a windows host so there is definitely a different behavior. Is this a difference in PyODBC or SQLAlchemy? I suspect the former, but good if you can confirm. the diff is in SA. Please look at my last patch to see why. This is because the support unicode flag is controlled by the test: supports_unicode = sys.maxunicode == 65535 and on Linux (mine at least) is equals to false, but on Windows it equals to True... my patch makes this flag always false on Windows and then all works as intended Florent. --~--~-~--~~~---~--~~ 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] spam
Hi moderators, Could we ban this Riaz M. from sending any more emails to this group? He also tried to send a lot of spam in TurboGears group and I moderated and banned him again there. Regards, Florent. --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
On Nov 8, 2007 7:30 PM, Paul Johnston [EMAIL PROTECTED] wrote: Hi, I have isolated the problem a little bit more: my column is defined in the MSSQL server as a user defined type which is based on VARCHAR. Ok, so in this case you'd like SA to return a python unicode object when a VARCHAR is fetched, by decoding using the database's encoding? While I yep :) understand your requirement, this seems to me to be a special case. I think most people would expect a normal string in this case. I wonder if you should define a MyString class in your app and use that. humm What bothers me is that I already get this comportement when running my query program from a Linux host (using pyodbc same version) but need the above mentioned patch on a windows host so there is definitely a different behavior. If we do decide to implement this, does anyone know how python can find out what database encoding MSSQL is using? in each mssql db there is a syscolumns table that references each column by name and has a collation column that give the corresponding collation name. This is far from perfect but I don't know better at the moment :-/. From my point of view I am responsible to give the engine the right encoding when I instantiate it. At the moment I have a master database that provides me this info and so I feed it to the constructor at engine creation time. Florent. --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
On Nov 8, 2007 1:36 PM, Florent Aide [EMAIL PROTECTED] wrote: [...] My patch then works because in fact the object that come back from sql server is a binary string encoded using the aforementioned 'codepage'. I had contact with Christophe de Vienne (see above) who has access to the same databases from the same proprietary editor and confirmed the issue. The patch works when the column is defined in the model as String (because Unicode still returns type str even with my patch). Regards, Florent. --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
Hi Paul and others, I attached a new patch to the ticket (#839). It corrects the comportements I have and I now receive unicode objects in all cases: either when I declared String or Unicode as the column type and whatever the type of my columns in MSSQL (varchar based or nvarchar based) I think this is more correct than the previous patch. BTW: I use ms sql2000, pyodbc 2.0.38. I have other issues with 2.0.39 and SA that I'll post about later on :) --~--~-~--~~~---~--~~ 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: unicode support for MSSQL
On Nov 5, 2007 6:50 PM, Paul Johnston [EMAIL PROTECTED] wrote: [...] Are your strings VARCHAR or NVARCHAR? If they're NVARCHAR, all this will just work as-is with SA and MSSQL - are you having any specific problems? If they're VARCHAR, then we need to think some more. I'm still not sure what the semantics should be when trying to save a unicode object in a non-unicode storage area. I have isolated the problem a little bit more: my column is defined in the MSSQL server as a user defined type which is based on VARCHAR. I tried to force the user defined type to be based on NVARCHAR and it works. BUT... I cannot do this in production since I don't own the dbs and they are used by other applications, I just have a read-only access on them to do some interrogations. My patch then works because in fact the object that come back from sql server is a binary string encoded using the aforementioned 'codepage'. I had contact with Christophe de Vienne (see above) who has access to the same databases from the same proprietary editor and confirmed the issue. Christophe also noted that in Linux since the supports_unicode resolves to False, we don't encounter problems. But since my code will run on Windows I am still searching for a way to sort that mess :) Thanks for the help and time Paul :-) Regards, Florent. --~--~-~--~~~---~--~~ 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] unicode support for MSSQL
Hi Mike, Hi all, Our conversation yesterday night on IRC gave me ideas :) I just added ticket #839 to the trac and attached a patch that enables unicode conversion for MSSQL dialects. I tested it with pyodbc but it should work the same with the other dialects. Best regards, Florent Aide. --~--~-~--~~~---~--~~ 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: TurboGears: best practices for SELECTing
On 10/11/07, James Brady [EMAIL PROTECTED] wrote: Did you call assign_mapper on Ownership? [...] However, I'm using the identity framework (part of TurboGears) which unfortunately doesn't play nicely with assign_mapper... shame! If you use SA 0.3.10 I would advise you to use mapper (from sqlalchemy.orm import mapper) instead of assign_mapper. If you could send a post in the TurboGears mailing list about why identity does not play nice with assign_mapper we would surely find a solution to your issue :) I used a lot of assign_mapper myself with TG a few months back, I now switched to mapper in order to become 0.4 compliant, but I can tell you TG 1.0.2/1.0.3.x are assign_mapper friendly... 1.0.4beta1 should also be usable with assign_mapper. Regards, Florent. --~--~-~--~~~---~--~~ 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: Problems with Sequence on PostgreSQL.
On 10/5/07, Gustavo Schneider [EMAIL PROTECTED] wrote:
Hi all SQLAlchemists,
Hi,
[...]
The code:
code
table1 = Table('table1', metadata,
Column('id', types.Integer, Sequence('table1_id_seq'), primary_key=True)
)
/code
When using postgresql you can have only this code and it creates the
sequence for you:
table1 = Table('table1', metadata,
Column('id', types.Integer, primary_key=True)
)
it will auto increment with a step of 1.
Florent.
--~--~-~--~~~---~--~~
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: interface error with Decimal(0) in where clause
Hi,
As far as I know, sqlite does not allow you to store decimal objects,
only floats. Which really is not the same. If you really need decimals
(ie: accounting books anyone ?) then you should consider using
firebird which is the only other database engine supported by SA that
is embeddable in a python application without the need of and external
server.
If someone has a way to accurately manipulate floats with the same
precision as decimals I would gladly hear from it because for the
moment I just banned sqlite from my dbengine choices for this
particular reason :(
Regards,
Florent.
On 8/22/07, cfriedalek [EMAIL PROTECTED] wrote:
I have two sqlite databases with the same table structure. I want to
combine them. I want to insert entries from the second into the first
if they don't already exist in the first. So I wrote this small script
(probably not the optimal way but I'm on the steep side of the
learning curve ... going up I hope.) Problem is I get an interface
error for a where clause when it includes a Decimal(0) entry. Seems
like a bug but I hesitate to submit a ticket when I'm so green.
Here's the code and database as sql. Run as combine_databases.py db1
db2 . If db1 and db2 are the same there should be a bunch of skipped
entry messages. Note I hacked the code at line 37 to get it to run for
the case of a Decimal(0) comparison in the where clase.
line 37 if trloc == 0: trloc = float(trloc) # hack to make
this work
So is this s bug or user error?
cf
import sys
import sqlalchemy as sa
import sqlalchemy.orm as orm
def update_db(table, temp, shape, sidx, nc, br, bi, axloc, trloc):
try:
keys = ['temp', 'shape', 'sidx','nc', 'br', 'bi',
'axloc','trloc']
vals = [temp, shape, sidx, nc, br, bi, axloc, trloc]
dic = dict(zip(keys, vals))
i = table.insert().execute(dic)
except:
print ERROR: failed to store data , dic, \n, temp, shape,
sidx, nc,\
if len(sys.argv) = 2:
sys.exit(Usage: supply two or more databases: 2nd ... inserted to
first)
dbs = sys.argv[1:]
engines = [sa.create_engine(''.join(('sqlite:///', db))) for db in
dbs]
metadatas = [sa.MetaData(engine) for engine in engines]
tables = [sa.Table('temperatures', md, autoload=True) for md in
metadatas]
import pdb;pdb.set_trace()
TABLE = tables[0]
ENGINE = engines[0]
for table, engine in zip(tables[1:], engines[1:]):
query = table.select()
for row in engine.execute(query):
temp = row[table.c.temp]
shape = row[table.c.shape]
sidx = row[table.c.sidx]
nc = row[table.c.nc]
br = row[table.c.br]
bi = row[table.c.bi]
axloc = row[table.c.axloc]
trloc = row[table.c.trloc]
#if trloc == 0: trloc = float(trloc) # hack to make this work
QUERY = TABLE.select().where(sa.and_(
TABLE.c.shape == shape, TABLE.c.sidx ==
sidx,
TABLE.c.nc == nc, TABLE.c.br == br,
TABLE.c.bi == bi, TABLE.c.axloc ==
axloc,
TABLE.c.trloc == trloc))
for ROW in ENGINE.execute(QUERY):
if ROW:
print .Skipped , row
else:
update_db(TABLE, temp, shape, sidx, nc, br, bi, axloc,
trloc)
print Added .. , row
BEGIN TRANSACTION;
CREATE TABLE temperatures (id INTEGER PRIMARY KEY, temp NUMERIC, shape
VARCHAR(10), sidx INTEGER, nc INTEGER, br NUMERIC, bi NUMERIC, axloc
NUMERIC, trloc NUMERIC);
INSERT INTO temperatures VALUES(1,-1.73316368036707e-05,'circle',
10,2000,NULL,NULL,1.0e-05,0);
INSERT INTO temperatures VALUES(2,4.60309299836984e-06,'circle',
10,2000,NULL,NULL,1.0e-05,0.01);
INSERT INTO temperatures VALUES(3,-2.76801870080499e-06,'circle',
10,2000,NULL,NULL,1.0e-05,0.02);
INSERT INTO temperatures VALUES(4,1.77485003202532e-06,'circle',
10,2000,NULL,NULL,1.0e-05,0.03);
INSERT INTO temperatures VALUES(5,-1.08068601149679e-06,'circle',
10,2000,NULL,NULL,1.0e-05,0.04);
INSERT INTO temperatures VALUES(6,5.37872557002763e-07,'circle',
10,2000,NULL,NULL,1.0e-05,0.05);
INSERT INTO temperatures VALUES(7,-8.82840026334908e-08,'circle',
10,2000,NULL,NULL,1.0e-05,0.06);
INSERT INTO temperatures VALUES(8,-2.95596726045961e-07,'circle',
10,2000,NULL,NULL,1.0e-05,0.07);
INSERT INTO temperatures VALUES(9,6.28273021881235e-07,'circle',
10,2000,NULL,NULL,1.0e-05,0.08);
INSERT INTO temperatures VALUES(10,-9.17917407031199e-07,'circle',
10,2000,NULL,NULL,1.0e-05,0.09);
INSERT INTO temperatures VALUES(11,1.16934196740049e-06,'circle',
10,2000,NULL,NULL,1.0e-05,0.1);
COMMIT;
--~--~-~--~~~---~--~~
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
[sqlalchemy] Re: interface error with Decimal(0) in where clause
How would you do something like this then: session.query.(LedgerLine).query(LedgerLine.base_amount.between(decimal1, decimal2)) the between() won't work since sqlite won't be able to compare your pickled amounts. Pickling cannot be an option in all cases particularly when you are storing amounts for accounting books... Florent. On 8/24/07, svilen [EMAIL PROTECTED] wrote: decimals.. u can use pickling? slower, yes. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
