[sqlalchemy] Re: invalid byte sequence for encoding:utf8 Postgresql
Solvedmy faultthe file i'm reading was latin1... and I was
using the standard open..
now I use:
self.in_file = codecs.open(self.filename, r, latin1)
after that it worked fine...
thanks
On 13 Feb, 16:50, Michael Bayer [EMAIL PROTECTED] wrote:
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: pymssql delete problem
Thanks, That seems to solve the problem. Cheers, François Rick Morrison wrote: 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 -~--~~~~--~~--~--~---
[sqlalchemy] How to load a complex data set into an SA aware object?
Hi, I'm working on a project where I got several read only tables that are dependent on a third-party, I got several vainlla SQL queries to get the data I need of them and I was wondering which will be the best way to load them up into SA. The queries themselfs are quite complex with several inner joins and other nasty SQL, the queries don't change except for 1 paramenter I need to pass in which is the root item i'm looking for. I was wondering if there was a way - I could create a class with no Table object that will be populated from the resulting query, - or if I should go with a db view and/or stored procedure, (how will I call that form sa?) - or if I should translate the raw query into SA's sqlexpresions - or should I just bypass SA and do a raw dbapi call? which will be the best way to handle this situation? Keep in mind this data is read-only so the only function I need is getInfo(itemId), which will execute the query and return Table-like object. --~--~-~--~~~---~--~~ 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
-Original Message- From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Chris Withers Sent: 13 February 2008 13:51 To: sqlalchemy@googlegroups.com Subject: [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? When you ask it to via metadata.drop_all() or table.drop(). Docs are here: http://www.sqlalchemy.org/docs/04/metadata.html#metadata_creating 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? See the docs referenced above. SQLAlchemy on its own doesn't use config files, but some of the frameworks that use it (eg. TurboGears and pylons) allow some SQLAlchemy-related configuration to be done in config files. 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? I don't know about generic comparisons, but the Migrate project might be a place to start: http://code.google.com/p/sqlalchemy-migrate/ (Note that it is only in the process of being updated for SA 0.4) Hope that helps, Simon --~--~-~--~~~---~--~~ 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
In other news, pyodbc on Unix is alive again, thanks to the surprise revelation that it actually can work, and shows a similar test profile to running it on Windows. Stay tuned... --~--~-~--~~~---~--~~ 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
so, does somebody want to add EXEC to the is_select() regexp ? I think we should also add a flag to text() which allows this too, along the lines of returns_results=True. On Feb 13, 2008, at 4:50 PM, Paul Johnston wrote: 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: How to load a complex data set into an SA aware object?
Jorge Vargas wrote:
Hi,
I'm working on a project where I got several read only tables that
are dependent on a third-party, I got several vainlla SQL queries to
get the data I need of them and I was wondering which will be the best
way to load them up into SA. The queries themselfs are quite complex
with several inner joins and other nasty SQL, the queries don't change
except for 1 paramenter I need to pass in which is the root item i'm
looking for.
I was wondering if there was a way
- I could create a class with no Table object that will be populated
from the resulting query,
- or if I should go with a db view and/or stored procedure, (how will
I call that form sa?)
- or if I should translate the raw query into SA's sqlexpresions
- or should I just bypass SA and do a raw dbapi call?
which will be the best way to handle this situation? Keep in mind this
data is read-only so the only function I need is getInfo(itemId),
which will execute the query and return Table-like object.
If you've already got the complex SQL and it's working for you, might as
well use it:
query = text('SELECT foo, bar FROM baz WHERE root_item = :root')
resultset = connection.execute(query, root=123)
The only modification of the original query needed is changing the
variable portion to a named bind parameter with the :colon syntax.
--~--~-~--~~~---~--~~
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: version_id_col question
On Feb 14, 2008, at 10:19 AM, Jonathan LaCour wrote: I'd like to automatically enable the SQLAlchemy concurrent modification checking on versioned mappers using the `version_id_col` argument, but the documentation isn't clear on how it works. Is this column managed by SQLAlchemy or is it to be managed by the user? Since our existing version column is managed by Elixir itself, I need to know if I can use it as the argument to `version_id_col` without SQLAlchemy and Elixir conflicting in who controls the column. its managed entirely by SQLAlchemy at the moment, starts at 1 and increments automatically, and acutually doesnt have any connection to class-based attributes so its a little insular. I would think that elixir could just move its own management of the version over to this mechanism but we can also look into opening up its behavior if that suits your functionality better. --~--~-~--~~~---~--~~ 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
Sure, I'll take care of it. Is there an easy way to side-step things like columns named 'exec', or is that just a risk we take? --~--~-~--~~~---~--~~ 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
the regexp is \s*(keywords) so it should only match EXEC as the first thing in the string. is the EXEC the only way to call an SP in MS-SQL ? no SELECT procname ? On Feb 14, 2008, at 12:05 PM, Rick Morrison wrote: Sure, I'll take care of it. Is there an easy way to side-step things like columns named 'exec', or is that just a risk we take? --~--~-~--~~~---~--~~ 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
Only rarely is there only one way to do something in MSSQL ;-) Stored procedures can also be called simply by name, omitting the EXEC: EXEC procedure_foo parms or procedure_foo parms and I believe they can also be called from within a subquery: select * from (procedure_foo) I think the EXEC-in-front case is probably our 90% use-case, and users can re-write as needed. --~--~-~--~~~---~--~~ 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
This approach would be ideal, and would work with row-returning functions, etc. but obviously depends on some rather sophisticated cooperation with the dbapi. I don't think pymssql would be up to the task, although I think the ODBC-derived dbapis might work. On Thu, Feb 14, 2008 at 12:11 PM, jason kirtland [EMAIL PROTECTED] wrote: Michael Bayer wrote: so, does somebody want to add EXEC to the is_select() regexp ? I think we should also add a flag to text() which allows this too, along the lines of returns_results=True. There was some talk of trying to auto-detect resultsets with cursor inspection. My recollection from poking at it was that results were promising on most db-apis, but server-side cursors remained to be tested. If that can't be 100% reliable then we would definitely need a hinting flag. --~--~-~--~~~---~--~~ 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 think we should also add a flag to text() which allows this too, along the lines of returns_results=True. +1 on that, it would be useful as a fallback for those oddball situations. --~--~-~--~~~---~--~~ 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
Stored procedures can also be called simply by name, omitting the EXEC: EXEC procedure_foo parms or procedure_foo parms True, but as you suggested it's hardly a burden to type the EXEC. and I believe they can also be called from within a subquery: select * from (procedure_foo) That would be lovely, but I can find no way to wrap a procedure in a select. If it were possible I could have just rewritten the procedure call as a select and matched the existing regexp. --~--~-~--~~~---~--~~ 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 think we should also add a flag to text() which allows this too, along the lines of returns_results=True. +1 on that, it would be useful as a fallback for those oddball situations. Indeed, Microsoft SQL Server interprets myriad bespoke SQL constructs which return results. Perhaps the MSSQLDialect.reflecttable() implementation should use the flag itself rather than special-case the regexp for sp_columns. Or it could just use EXEC presuming it is added to the regexp. --~--~-~--~~~---~--~~ 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
Michael Bayer wrote: so, does somebody want to add EXEC to the is_select() regexp ? I think we should also add a flag to text() which allows this too, along the lines of returns_results=True. There was some talk of trying to auto-detect resultsets with cursor inspection. My recollection from poking at it was that results were promising on most db-apis, but server-side cursors remained to be tested. If that can't be 100% reliable then we would definitely need a hinting flag. --~--~-~--~~~---~--~~ 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
Rick Morrison wrote: Only rarely is there only one way to do something in MSSQL ;-) Stored procedures can also be called simply by name, omitting the EXEC: EXEC procedure_foo parms or procedure_foo parms True, as long as the call is the first statement in the batch; otherwise, you need the exec. and I believe they can also be called from within a subquery: select * from (procedure_foo) No, but mssql has the concept of table-valued user defined function, so you could have something like select * from dbo.foo(@var) -- think of it as a parameterized view. I agree that something like the returns_results hint might be a good way to go. -- 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] Re: version_id_col question
Michael Bayer wrote: its managed entirely by SQLAlchemy at the moment, starts at 1 and increments automatically, and acutually doesnt have any connection to class-based attributes so its a little insular. I would think that elixir could just move its own management of the version over to this mechanism but we can also look into opening up its behavior if that suits your functionality better. I am actually considering just adding an additional `concurrent_version` column so that the two can just not clobber one another... I might take you up on that though, depending on how that goes... -- Jonathan LaCour http://cleverdevil.org --~--~-~--~~~---~--~~ 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
Rick Morrison wrote: This approach would be ideal, and would work with row-returning functions, etc. but obviously depends on some rather sophisticated cooperation with the dbapi. I don't think pymssql would be up to the task, although I think the ODBC-derived dbapis might work. It's not that fancy: just checking for the cursor.description attribute. There needs to be some work done in this area anyhow. I noticed that the MySQLdb db-api crashes if a stored procedure returns multiple result sets and nextset() isn't called for all of them... so we'd want to be able to detect a pending resultset in any case. And support procedures returning multiple resultsets in general. --~--~-~--~~~---~--~~ 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] 0.3.x eagerload error
I'm having some trouble configuring eager loading for a query:
Query:
domainq =
ctx.current.query(Domain).options(eagerload('company')).all()
Mapper:
domainmapper = mapper(Domain, domains, extension=ModifiedMapper(),
properties={
'company' : relation(Company, uselist=False),
}
)
Error:
type 'exceptions.AttributeError': 'str' object has no attribute
'get_children'
It works fine if I remove the options(eagerload(...)) part...
thx
m
--~--~-~--~~~---~--~~
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] now() function + MSSQL
Hey I noticed just now when I was updating the CHANGES file that MSSQL should be included in the generic func.now() -- CURRENT_TIMESTAMP group. that's in r4161 --~--~-~--~~~---~--~~ 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: 0.3.x eagerload error
On Feb 14, 2008, at 12:48 PM, Matt wrote:
I'm having some trouble configuring eager loading for a query:
Query:
domainq =
ctx.current.query(Domain).options(eagerload('company')).all()
Mapper:
domainmapper = mapper(Domain, domains, extension=ModifiedMapper(),
properties={
'company' : relation(Company, uselist=False),
}
)
Error:
type 'exceptions.AttributeError': 'str' object has no attribute
'get_children'
It works fine if I remove the options(eagerload(...)) part...
theres nothing wrong with that code and the error message would
suggest some issue in constructing the outerjoins used by the
eagerload, so would have to see the Table constructs in use (as well
as a stack trace).
--~--~-~--~~~---~--~~
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
And support procedures returning multiple resultsets in general. That would be great, although I think such things are pretty poor form. Years ago I worked on a legacy system that had a calc procedure returning 20+ result sets, and a variable number of them at that. What a nightmare that was trying to keep all that straight. But it happens. --~--~-~--~~~---~--~~ 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 14, 2008, at 6:01 PM, Waldemar Osuch wrote: On Feb 14, 2:06 pm, Richard Jones [EMAIL PROTECTED] wrote: On Feb 14, 5:13 pm, Waldemar Osuch [EMAIL PROTECTED] wrote: On Feb 13, 8:03 pm, Richard Jones [EMAIL PROTECTED] wrote: Thanks, I should have been clearer: how is ID generation done when there is no sequence assigned to the column? Then my guess is that the generation does not happen and you have to provide the value of the ID yourself. One could have an insert trigger doing the work but then SA would not know about the new ID and you would have to flush, clear and requery before being able to add related record. Any particular reason you do not want to specify the Sequence in the definition? Anyway enough with the guesses. Time for an expert to step in :-) if you wanted to use a trigger or something, the issue with Oracle specifically is that cx_oracle provides no way of immediately retreiving a newly generated primary key default. So we use sequences so that the sequence can be pre-executed before each INSERT and we then have the new ID. If you had some other function which generates an ID that can also be specified using a ColumnDefault object; but the limitation is that it needs to be something which can run standalone (i.e. not solely a trigger on the table). Also with Oracle we don't any implicit id generation scheme; you have to give it something explicitly, either a sequence, ColumnDefault, or always provide primary key identifiers explicitly in the INSERT parameters. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
