Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?
On Mon, Oct 8, 2018 at 9:56 AM Mike Bayer wrote:
>
>
> On Mon, Oct 8, 2018 at 10:28 AM Lukasz Szybalski
> wrote:
>
>>
>> I have a query in sqlalchemy like below where I lookup contract# in
>>>>>> mssql. How do I enforce the varchar instead of nvarchar? I tried
>>>>>> converting
>>>>>> my field to "str(mycurrent)" but that didn't do anything. Is there some
>>>>>> other spot to force VARCHAR to be sent?
>>>>>>
>>>>>
>>>>>
>>>>> this just came up in
>>>>> https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case,
>>>>> in that case they are using typed parameters:
>>>>>
>>>>> String(50, convert_unicode='force')
>>>>>
>>>>> if you're dealing w/ the raw string like that try this:
>>>>>
>>>>> execute(text("select ... where foo =
>>>>> :mycurrent").bindparams(bindparam("mycurrent",
>>>>> type_=String(convert_unicode='force')))
>>>>>
>>>>> let me know if that works b.c. this has to be in the docs
>>>>>
>>>>
>
> you need to use the text() construct as I illustrated earlier:
>
> session.execute(
> text("select ... where foo =
> :mycurrent").bindparams(bindparam("mycurrent",
> type_=String(convert_unicode='force')),
> params={"mycurrent": "value"}
> )
>
>
>
>
>
>
>>
>>
>>> You can find that the query that uses NVARCHAR does an index scan has
>>> 30,909 logical reads on the dbo.P table. It also uses 890 ms of CPU and
>>> has a total elapsed time of 938 ms.
>>>
>>> The query that uses VARCHAR does an index seek and has 7 logical reads
>>> on the dbo.P table. It uses 0 ms of CPU and has a total elapsed time of 11
>>> ms.
>>>
>>>
>>> p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P
>>> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
>>> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
>>> where p.current=:mycurrent and D.srtype_id
>>> =5",params={'mycurrent':str(mycurrent)}).fetchall()
>>>
>>>
>>>
>>>
>>>
>>> [image: Inline image 1]
>>>
>>>
>>>
>>>
SUPER FAST..now.!!!
(I wonder what would be a side effect if this was done by default? aka user
varchar)
For Reference: I'll I split into two lines because for some reason I was
making some mistake and had to google my way out of it.
Version1 (easier to understand and test)
*from sqlalchemy import text, bindparam,String*
# ":mycontract" and "mycontract" parts bound by the bindparams to create a
statemetn query.
*# item in bold is what is required to get it to convert from nvarchar to
varchar*
stmt= text("select P.contract_id from dbo.P P with(NOLOCK) where
currentstatus_id in (1,2) and current_cuntract=:mycontract").
*bindparams(bindparam("mycontract",type_=String(50,convert_unicode='force')))*
#We now execute the statement from above and bind it to a variable I'm
passing row.mycontract from our forloop.
p=session.execute(stmt,params={'mycontract':row.mycontract}).fetchall()
This causes the value to be varchar instead of nvarchar which fixes mssql :
"NVARCHAR cause performance downgrade because NVARCHAR literals do not use
VARCHAR indexes. When we add convert_unicode='force' all our N'something'
turns into 'something' and gains performance from indexes."
https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case
**Can you post above as comment to bitbucket ticket. I can't register it
says "are you sure you are not a robot" with no other option ;) lol . I
guess they don't get many new accounts lol
Thank you sqlalchemy!
Lucas
--
http://lucasmanual.com/ <http://lucasmanual.com/blog/>
>
>>
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>
--
http://lucasmanual.com/ <http://lucasmanual.com/blog/>
--
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.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?
> I have a query in sqlalchemy like below where I lookup contract# in mssql.
How do I enforce the varchar instead of nvarchar? I tried converting my
field to "str(mycurrent)" but that didn't do anything. Is there some other
spot to force VARCHAR to be sent?
>>>
>>>
>>> this just came up in
>>> https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case,
>>>
>>> in that case they are using typed parameters:
>>>
>>> String(50, convert_unicode='force')
>>>
>>> if you're dealing w/ the raw string like that try this:
>>>
>>> execute(text("select ... where foo =
>>> :mycurrent").bindparams(bindparam("mycurrent",
>>> type_=String(convert_unicode='force')))
>>>
>>> let me know if that works b.c. this has to be in the docs
>>>
>>
>>
>>
>>
Hello Mike.
Do you have a minute to guide me on the syntax. I can't figure this out,
and I need to fix this performance problem. If I can't figure it out I'll
just create stored procedures to replace the query.
Here is the code again:
# 'm struggling with the syntax here for the bind param. How do I convert
from session.execut(stmt,params) to your bindparams? I can't just cast the
variable I'm passing to correct format?
# Keep in mind that the value is either some sqlalchemy object.columname or
some 'other system' variable from a loop aka (for row in First_query:
mycontract=row.contract_no
#I need to go from to :
>
> From
> p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P with(nolock)
> inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner join dbo.D D
> with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num where
> p.contract=:mycontract and D.srtype_id
> =5",params={'mycontract':str(mycontract)}).fetchall()
>
> To:
> from sqlalchmy import bindparams
> ##mycurrent=someobject.contract_no
> mycurrent='ABC123'
>
> I tried these syntax but it doesn;t work:
#Incorrect Syntax#1?
>
> (bad) p=session.execute(text("select PZ.p_id,PZ.pimage_num from dbo.P
> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
> where p.contract=:mycontract and D.srtype_id
> =5").bindparam('mycontract'),params={'mycontract':str(mycontract)}).fetchall()
> AttributeError: 'TextClause' object has no attribute 'bindparam'
>
> #Incorrect Syntax#2?
> (bad) p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P
> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
> where p.contract=:mycontract and D.srtype_id
> =5").bindparam('mycontract').fetchall()
> A value is required for bind parameter 'mycontract'
>
>
> #Incorrect Syntax #3?
This in the link
indicated.
Page says that a "String(50).with_variant(String(50,
convert_unicode='force'), 'mssql')" has worked for Jan K. but it doesn't
really say how does somebody converts their variable into correct type
string:
> like:
> mycontract=cast(mycontract, String(convert_unicode='force'))
>
>
> #Incorrect Syntax #4?
> Since I'm passing params right after my statement should the bindparam be
> in params section?
> something along:
> params={'mycontract':sqlalchemy.string(mycontract).convert_unicode='force')})
>
> The easiest way would be to convert mycontract into proper
> String(convert_unicode='force') and pass mycontract as in my original query?
>
>
>
> You can find that the query that uses NVARCHAR does an index scan has
> 30,909 logical reads on the dbo.P table. It also uses 890 ms of CPU and
> has a total elapsed time of 938 ms.
>
> The query that uses VARCHAR does an index seek and has 7 logical reads on
> the dbo.P table. It uses 0 ms of CPU and has a total elapsed time of 11 ms.
>
>
> p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P
> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
> where p.current=:mycurrent and D.srtype_id
> =5",params={'mycurrent':str(mycurrent)}).fetchall()
>
>
>
>
>
> [image: Inline image 1]
>
>
>
>
--
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.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql
On Friday, April 27, 2018 at 4:05:20 PM UTC-5, Lukasz Szybalski wrote:
>
>
>
> On Thursday, January 18, 2018 at 11:49:39 AM UTC-6, Mike Bayer wrote:
>>
>>
>>
>> On Thu, Jan 18, 2018 at 12:31 PM, Lukasz Szybalski
>> wrote:
>>
>>> Hello,
>>> I have a query in sqlalchemy like below where I lookup contract# in
>>> mssql. How do I enforce the varchar instead of nvarchar? I tried converting
>>> my field to "str(mycurrent)" but that didn't do anything. Is there some
>>> other spot to force VARCHAR to be sent?
>>>
>>
>>
>> this just came up in
>> https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case,
>>
>> in that case they are using typed parameters:
>>
>> String(50, convert_unicode='force')
>>
>> if you're dealing w/ the raw string like that try this:
>>
>> execute(text("select ... where foo =
>> :mycurrent").bindparams(bindparam("mycurrent",
>> type_=String(convert_unicode='force')))
>>
>> let me know if that works b.c. this has to be in the docs
>>
>
>
>
> Hello,
Sorry for late response. I'm struggling with the syntax here for the bind
param. How do I convert from session.execut(stmt,params) to your
bindparams? I can't just cast the variable I'm passing to correct format?
Knowing that the value I will be passing is either some sa stored procedure
sqlalchemy object.columname or some other system
mycontract=someobject.contract_no
From
p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P with(nolock)
inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner join dbo.D D
with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num where
p.contract=:mycontract and D.srtype_id
=5",params={'mycontract':str(mycontract)}).fetchall()
To:
from sqlalchmy import bindparams
##mycurrent=someobject.contract_no
mycurrent='ABC123'
(bad) p=session.execute(text("select PZ.p_id,PZ.pimage_num from dbo.P
with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
where p.contract=:mycontract and D.srtype_id
=5").bindparam('mycontract'),params={'mycontract':str(mycontract)}).fetchall()
AttributeError: 'TextClause' object has no attribute 'bindparam'
(bad) p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P
with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
where p.contract=:mycontract and D.srtype_id
=5").bindparam('mycontract').fetchall()
A value is required for bind parameter 'mycontract'
- What is the final working syntax, I can't find any examples of how to fix
this in the link
<https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case>
indicated.
Page says that a "String(50).with_variant(String(50,
convert_unicode='force'), 'mssql')" has worked for Jan K. but it doesn't
really say how does somebody converts their variable into correct type
string:
like:
mycontract=cast(mycontract, String(convert_unicode='force'))
Since I'm passing params right after my statement should the bindparam be
in params section?
something along:
params={'mycontract':sqlalchemy.string(mycontract).convert_unicode='force')})
The easiest way would be to convert mycontract into proper
String(convert_unicode='force') and pass mycontract as in my original query?
Thank you
Lucas
__
http://lucasmanual.com
>
>>>
>>> You can find that the query that uses NVARCHAR does an index scan has
>>> 30,909 logical reads on the dbo.P table. It also uses 890 ms of CPU and
>>> has a total elapsed time of 938 ms.
>>>
>>> The query that uses VARCHAR does an index seek and has 7 logical reads
>>> on the dbo.P table. It uses 0 ms of CPU and has a total elapsed time of 11
>>> ms.
>>>
>>>
>>> p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P
>>> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
>>> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
>>> where p.current=:mycurrent and D.srtype_id
>>> =5",params={'mycurrent':str(mycurrent)}).fetchall()
>>>
>>>
>>>
>>>
>>>
>>> [image: Inline image 1]
>>>
>>>
>>>
>>>
>>> Thank you
>>>
>>> Lucas
>>>
>>>
>>>
>>>
>>> --
>>> http://lucasmanual.com/ <http://lucasmanual.com/blog/>
>>>
>>> --
>>> SQLAlchemy -
>>> T
Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql
On Thursday, January 18, 2018 at 11:49:39 AM UTC-6, Mike Bayer wrote:
>
>
>
> On Thu, Jan 18, 2018 at 12:31 PM, Lukasz Szybalski <szyb...@gmail.com
> > wrote:
>
>> Hello,
>> I have a query in sqlalchemy like below where I lookup contract# in
>> mssql. How do I enforce the varchar instead of nvarchar? I tried converting
>> my field to "str(mycurrent)" but that didn't do anything. Is there some
>> other spot to force VARCHAR to be sent?
>>
>
>
> this just came up in
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case,
>
> in that case they are using typed parameters:
>
> String(50, convert_unicode='force')
>
> if you're dealing w/ the raw string like that try this:
>
> execute(text("select ... where foo =
> :mycurrent").bindparams(bindparam("mycurrent",
> type_=String(convert_unicode='force')))
>
> let me know if that works b.c. this has to be in the docs
>
Hello,
Sorry for late response. I'm struggling with the syntax here for the bind
param. How do I convert from session.execut(stmt,params) to your
bindparams? I can't just cast the variable I'm passing to correct format?
Knowing that the value I will be passing is either some sa stored procedure
sqlalchemy object.columname or some other system
mycontract=someobject.contract_no
From
p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P with(nolock)
inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner join dbo.D D
with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num where
p.contract=:mycontract and D.srtype_id
=5",params={'mycontract':str(mycontract)}).fetchall()
To:
from sqlalchmy import bindparams
##mycurrent=someobject.contract_no
mycurrent='ABC123'
(bad) p=session.execute(text("select PZ.p_id,PZ.pimage_num from dbo.P
with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
where p.contract=:mycontract and D.srtype_id
=5").bindparam('mycontract'),params={'mycontract':str(mycontract)}).fetchall()
AttributeError: 'TextClause' object has no attribute 'bindparam'
(bad) p=session.execute("select PZ.p_id,PZ.pimage_num from dbo.P
with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
where p.contract=:mycontract and D.srtype_id
=5").bindparam('mycontract').fetchall()
A value is required for bind parameter 'mycontract'
- What is the final working syntax, I can't find any examples of how to fix
this in the link
<https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case>
indicated.
Page says that a "String(50).with_variant(String(50,
convert_unicode='force'), 'mssql')" has worked for Jan K. but it doesn't
really say how does somebody converts their variable into correct type
string:
like:
mycontract=cast(mycontract, String(convert_unicode='force'))
Since I'm passing params right after my statement should the bindparam be
in params section?
something along:
params={'mycontract':sqlalchemy.string(mycontract).convert_unicode='force')})
The easiest way would be to convert mycontract into proper
String(convert_unicode='force') and pass mycontract as in my original query?
Thank you
Lucas
__
http://lucasmanual.com
>
>>
>> You can find that the query that uses NVARCHAR does an index scan has
>> 30,909 logical reads on the dbo.P table. It also uses 890 ms of CPU and
>> has a total elapsed time of 938 ms.
>>
>> The query that uses VARCHAR does an index seek and has 7 logical reads on
>> the dbo.P table. It uses 0 ms of CPU and has a total elapsed time of 11 ms.
>>
>>
>> p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P
>> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner
>> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num
>> where p.current=:mycurrent and D.srtype_id
>> =5",params={'mycurrent':str(mycurrent)}).fetchall()
>>
>>
>>
>>
>>
>> [image: Inline image 1]
>>
>>
>>
>>
>> Thank you
>>
>> Lucas
>>
>>
>>
>>
>> --
>> http://lucasmanual.com/ <http://lucasmanual.com/blog/>
>>
>> --
>> 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
[sqlalchemy] NVARCHAR vs VARCHAR in mssql
Hello,
I have a query in sqlalchemy like below where I lookup contract# in mssql.
How do I enforce the varchar instead of nvarchar? I tried converting my
field to "str(mycurrent)" but that didn't do anything. Is there some other
spot to force VARCHAR to be sent?
You can find that the query that uses NVARCHAR does an index scan has
30,909 logical reads on the dbo.P table. It also uses 890 ms of CPU and
has a total elapsed time of 938 ms.
The query that uses VARCHAR does an index seek and has 7 logical reads on
the dbo.P table. It uses 0 ms of CPU and has a total elapsed time of 11 ms.
p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P with(nolock)
inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner join dbo.D D
with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num where
p.current=:mycurrent and D.srtype_id
=5",params={'mycurrent':str(mycurrent)}).fetchall()
[image: Inline image 1]
Thank you
Lucas
--
http://lucasmanual.com/
[sqlalchemy] Re: DeferredReflection and mapper
On Wednesday, August 14, 2013 12:10:12 AM UTC-5, Lukasz Szybalski wrote:
Hello,
How do I go from class like defeinition to below with mapper.
The docs in 0.8 say I can use:
from sqlalchemy.ext.declarative import DeferredReflectionBase =
declarative_base()
class MyClass(DeferredReflection, Base):
__tablename__ = 'mytable'
but how do I do below with DefferedReflection
--
from sqlalchemy import Table
from sqlalchemy.orm import mapper, relation
class Recall(object):
def __init__(self, **kw):
automatically mapping attributes
for key, value in kw.iteritems():
setattr(self, key, value)
recall_table = Table('recall_db', metadata,
autoload=True,autoload_with=engine)
mapper(Recall, recall_table,primary_key=[recall_table.c.RECORD_ID])
I'm using pyramid, and I want to autoload tables in models.py which does
not have the engine bound yet. I will bind in in main function with
DeferredReflection.prepare(engine)
Never mind:
I followed the documentation:
In models.py I did
from sqlalchemy.ext.declarative import DeferredReflection
class Recall(DeferredReflection, Base):
__tablename__ = 'recall_db'
in __init__.py I did
from sqlalchemy.ext.declarative import DeferredReflection
then under
Base.metadata.bind = engine
add
DeferredReflection.prepare(engine)
I can access the table with
Recall.__table__
and mapper with
Recall.__mapper__
I presume this does the same as :
recall_table = Table('recall_db', metadata, autoload=True,autoload_with=
engine)
class Recall(object):
def __init__(self, **kw):
automatically mapping attributes
for key, value in kw.iteritems():
setattr(self, key, value)
mapper(Recall, recall_table,primary_key=[recall_table.c.RECORD_ID])
Thanks
Lucas
--
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] DeferredReflection and mapper
Hello,
How do I go from class like defeinition to below with mapper.
The docs in 0.8 say I can use:
from sqlalchemy.ext.declarative import DeferredReflectionBase =
declarative_base()
class MyClass(DeferredReflection, Base):
__tablename__ = 'mytable'
but how do I do below with DefferedReflection
--
from sqlalchemy import Table
from sqlalchemy.orm import mapper, relation
class Recall(object):
def __init__(self, **kw):
automatically mapping attributes
for key, value in kw.iteritems():
setattr(self, key, value)
recall_table = Table('recall_db', metadata,
autoload=True,autoload_with=engine)
mapper(Recall, recall_table,primary_key=[recall_table.c.RECORD_ID])
I'm using pyramid, and I want to autoload tables in models.py which does
not have the engine bound yet. I will bind in in main function with
DeferredReflection.prepare(engine)
Thanks
Lucas
--
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.
Re: [sqlalchemy] sqlalchemy.exc.DBAPIError: (Error) ('HY003', '[HY003] [FreeTDS][SQL Server]Program type out of range (0) (SQLGetData)') None None
On Friday, November 16, 2012 7:52:00 PM UTC-6, Michael Bayer wrote:
On Nov 16, 2012, at 11:44 AM, Lukasz Szybalski wrote:
Hello,
Any idea what this error message means.
I'm trying to execute this:
s=session.execute(assp_Checks @begin_date=:start,
@end_date=:end,@company_id=1,params={'start':date_to_process,'end':date_to_process}).fetchall()
I get:
Traceback (most recent call last):
File stdin, line 1, in module
File stdin, line 6, in process_date
File
/home/unique/checkexport/env26/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 3210, in fetchall
self.cursor, self.context)
File
/home/unique/checkexport/env26/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 3204, in fetchall
l = self.process_rows(self._fetchall_impl())
File
/home/unique/checkexport/env26/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 3171, in _fetchall_impl
return self.cursor.fetchall()
sqlalchemy.exc.DBAPIError: (Error) ('HY003', '[HY003] [FreeTDS][SQL
Server]Program type out of range (0) (SQLGetData)') None None
If I use isql I have no problems.
exec assp_Checks @begin_date='11/15/2012',
@end_date='11/15/2012',@company_id=1
I get 10 records back.
I don't have a solution for this but it is certainly due to the reduced
featureset present in ODBC, particularly that of FreeTDS.You'd be
looking here to find a path to getting this to work with pyodbc directly,
and perhaps you'd need to email on the freetds list and/or change your
approach.
Just confirming nothing change in sqlalchemy.
This code has worked in sa in debian old stable, but for some reason when
I've upgraded OS, freeTDS, and sqlalchemy I suddenly got that error. I'll
check by using pure pyodbc and will contact freeTDS then.
Thanks for the guidance.
Lucas
--
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/-/-c-8YwLnhUYJ.
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.exc.DBAPIError: (Error) ('HY003', '[HY003] [FreeTDS][SQL Server]Program type out of range (0) (SQLGetData)') None None
Hello,
Any idea what this error message means.
I'm trying to execute this:
s=session.execute(assp_Checks @begin_date=:start,
@end_date=:end,@company_id=1,params={'start':date_to_process,'end':date_to_process}).fetchall()
I get:
Traceback (most recent call last):
File stdin, line 1, in module
File stdin, line 6, in process_date
File
/home/unique/checkexport/env26/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 3210, in fetchall
self.cursor, self.context)
File
/home/unique/checkexport/env26/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 3204, in fetchall
l = self.process_rows(self._fetchall_impl())
File
/home/unique/checkexport/env26/lib/python2.6/site-packages/SQLAlchemy-0.7.9-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py,
line 3171, in _fetchall_impl
return self.cursor.fetchall()
sqlalchemy.exc.DBAPIError: (Error) ('HY003', '[HY003] [FreeTDS][SQL
Server]Program type out of range (0) (SQLGetData)') None None
If I use isql I have no problems.
exec assp_Checks @begin_date='11/15/2012',
@end_date='11/15/2012',@company_id=1
I get 10 records back.
Any ideas?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] mssql and specifying a schema name?
Hello,
I'm trying to autolaod my table image but it keeps complaining that the
table doesn't exists.
I've enabled the echo = true and I see that you specify in the query:
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT],
[COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image',
'MyDatabase'
)
But my schema name is dbo?
Where do I specify that?
On create_engine?
or?
import sqlalchemy
e = sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
from sqlalchemy.orm import mapper
#---
image_table = sqlalchemy.Table('image', metadata, autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: mssql and specifying a schema name?
On May 30, 1:03 pm, Michael Bayer mike...@zzzcomputing.com wrote:
the default schema name is determined by:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = (SELECT user_name())
AND type = 'S'
for some reason on your system it's coming up as MyDatabase. You'd want to
fix that so that it comes up with dbo.
I can't.
The database was design for a specific application and I cannot make
changes to it structure, other then my records.
Is there a _somevalue that stores this in my engine, so that I can
overwrite it? or is it automatically get pulled every time I do a
autoload=True?
Thanks,
Lucas
On May 30, 2012, at 1:52 PM, Lukasz Szybalski wrote:
Hello,
I'm trying to autolaod my table image but it keeps complaining that the
table doesn't exists.
I've enabled the echo = true and I see that you specify in the query:
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT],
[COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image',
'MyDatabase'
)
But my schema name is dbo?
Where do I specify that?
On create_engine?
or?
import sqlalchemy
e = sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
from sqlalchemy.orm import mapper
#---
image_table = sqlalchemy.Table('image', metadata, autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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: mssql and specifying a schema name?
On May 30, 1:03 pm, Michael Bayer mike...@zzzcomputing.com wrote:
the default schema name is determined by:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = (SELECT user_name())
AND type = 'S'
for some reason on your system it's coming up as MyDatabase. You'd want to
fix that so that it comes up with dbo.
Actually,
Let me try a different username.
That works. The username I was using was admin...so I logged in as
me and it has schema name of dbo.
Thanks,
Lucas
On May 30, 2012, at 1:52 PM, Lukasz Szybalski wrote:
Hello,
I'm trying to autolaod my table image but it keeps complaining that the
table doesn't exists.
I've enabled the echo = true and I see that you specify in the query:
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT],
[COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ? ORDER
BY [COLUMNS_1].[ORDINAL_POSITION]
2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image',
'MyDatabase'
)
But my schema name is dbo?
Where do I specify that?
On create_engine?
or?
import sqlalchemy
e = sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
from sqlalchemy.orm import mapper
#---
image_table = sqlalchemy.Table('image', metadata, autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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: mssql and specifying a schema name?
On May 30, 2:35 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On May 30, 2012, at 3:24 PM, Lukasz Szybalski wrote:
On May 30, 1:03 pm, Michael Bayer mike...@zzzcomputing.com wrote:
the default schema name is determined by:
SELECT default_schema_name FROM
sys.database_principals
WHERE name = (SELECT user_name())
AND type = 'S'
for some reason on your system it's coming up as MyDatabase. You'd want
to fix that so that it comes up with dbo.
I can't.
The database was design for a specific application and I cannot make
changes to it structure, other then my records.
Is there a _somevalue that stores this in my engine, so that I can
overwrite it? or is it automatically get pulled every time I do a
autoload=True?
then you'd need to put schema='dbo' into each of your Table objects. the
autoload should work fine with that.
Where would I put the schema='db'?
somewhere inside this statement ?
image_table = sqlalchemy.Table('image', metadata, autoload=True) ?
or?
[SQL Server]Login failed. The login is from an untrusted domain and
cannot be used with Windows authentication.
Thanks,
Lucas
On May 30, 2012, at 1:52 PM, Lukasz Szybalski wrote:
Hello,
I'm trying to autolaod my table image but it keeps complaining that the
table doesn't exists.
I've enabled the echo = true and I see that you specify in the query:
SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME],
[COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE],
[COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION],
[COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT],
[COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
ORDER BY [COLUMNS_1].[ORDINAL_POSITION]
INFO:sqlalchemy.engine.base.Engine:SELECT [COLUMNS_1].[TABLE_SCHEMA],
[COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME],
[COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH],
[COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?
ORDER BY [COLUMNS_1].[ORDINAL_POSITION]
2012-05-30 12:39:06,193 INFO sqlalchemy.engine.base.Engine ('image',
'MyDatabase'
)
But my schema name is dbo?
Where do I specify that?
On create_engine?
or?
import sqlalchemy
e = sqlalchemy.create_engine(mssql+pyodbc://Me:myPassword@SQLServer2008)
#e.echo=True
e.echo=False
metadata=sqlalchemy.MetaData(e)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, autocommit=False)
session = Session()
from sqlalchemy.orm import mapper
#---
image_table = sqlalchemy.Table('image', metadata, autoload=True)
???Where do specify my schema dbo? so instead of sending 'image',
'MyDatabase'...you send 'image','dbo'?
--
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: how to run a stored procedure?
Hello,
What if there is a column that is called state
When I try to add use_labels inside the execute statement I get below
error. Should I be using the use_labels somewhere else?:
a[0].State
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/engine/base.py, line 1943, in
__getattr__
return self[name]
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/engine/base.py, line 1934, in
__getitem__
try 'use_labels' option on select statement. % key)
sqlalchemy.exc.InvalidRequestError: Ambiguous column name 'State' in
result set! try 'use_labels' option on select statement.
# ---unexpected keyword error
a=session.execute(assp_ReportPolicyTransactions @start_date=:start,
@end_date=:end,params={'start':20100701,'end':20100719},use_labels=True).fetchall()
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 732, in
execute
engine = self.get_bind(mapper, clause=clause, **kw)
TypeError: get_bind() got an unexpected keyword argument 'use_labels'
Thank you,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to run a stored procedure?
how is calling query(colmames)...all() significantly different from simply
saying execute(..).fetchall() ? you get a list of named-tuple like objects
in both cases.
You are correct, execute().fetchall() does already returns a list of
rows, where each row has attributes. I was initially using execute()
and couldn't pass in the myresults[5] so I assumed this was returning
a list of results strings and not tuple, but they do.
I will use fetchall, and I am able to use myresults[5].customername
I'll update my docs with these example.
http://lucasmanual.com/mywiki/PythonManual#sqlalchemyandmssql
I wonder if passing arguments like this is mssql specific
(@some_input_parameter) , or it can be reused in other databases, like
mysql or postgre?
myresults=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks a lot.
Lucas
--
How to setup unix odbc for use with sqlalchemy and mssql
http://lucasmanual.com/mywiki/unixODBC
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to run a stored procedure?
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
I need to pass in the objects a[5] or do
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
new_record=a[5]
print new_record.customername
#call a function by passing the object
process_client(new_record)
#the process_client function can access records like
new_record.clientname,new_record.transaction_date
right now I get:
a[5]
Traceback (most recent call last):
File stdin, line 1, in module
TypeError: 'ResultProxy' object is unindexable
Do I convert the return or I use a different command?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1453, in all
return list(self)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1565, in
__iter__
return self._execute_and_instances(context)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1570, in
_execute_and_instances
mapper=self._mapper_zero_or_none())
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 276, in
_mapper_zero_or_none
if not getattr(self._entities[0], 'primary_entity', False):
IndexError: list index out of range
Thanks,
Lucas
I need to pass in the objects a[5] or do
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
new_record=a[5]
print new_record.customername
#call a function by passing the object
process_client(new_record)
#the process_client function can access records like
new_record.clientname,new_record.transaction_date
right now I get:
a[5]
Traceback (most recent call last):
File stdin, line 1, in module
TypeError: 'ResultProxy' object is unindexable
Do I convert the return or I use a different command?
Thanks,
Lucas
--
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
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 5:10 PM, Lukasz Szybalski wrote:
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
query() needs to have entities. Here's an example:
http://www.sqlalchemy.org/docs/ormtutorial.html#using-literal-sql
Can I pass in a list or some other type, I have over 30 columns?
a=session.execute(assp_ReportDailyTransactions @start_date=:start,
@end_date=:end,params={'start':20100701,'end':20100719})
b=session.query(a.keys()).from_statement(exec
assp_ReportDailyTransactions @start_date=:start,
@end_date=:end).params({'start':20100701,'end':20100719}).all()
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 873, in
query
return self._query_cls(entities, self, **kwargs)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 92, in
__init__
self._set_entities(entities)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 99, in
_set_entities
entity_wrapper(self, ent)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 2584, in
__init__
expected - got '%r' % column
sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
entity expected - got '[u'customer', u'customer_id', u'customer_num',
u'TransactionDate'..]
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 6:02 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 6:19 PM, Lukasz Szybalski wrote:
On Jul 20, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 5:10 PM, Lukasz Szybalski wrote:
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
query() needs to have entities. Here's an example:
http://www.sqlalchemy.org/docs/ormtutorial.html#using-literal-sql
Can I pass in a list or some other type, I have over 30 columns?
Its not clear what you are asking for. execute().fetchall() already
returns a list of rows, where each row has attributes, so that you can say
row.attrname, so it is already like an object. If you use a query(),
you have the choice of specifying an ORM mapped class or individual columns
as well, though if you are querying for all individual columns there's not
much difference between query(x, y, z).all() versus using
execute().fetchall().
When you say change it, if that means, I'd like to set attributes on the
resulting objects and they go back to the database, that's clearly not
possible unless you can relate your stored procedure rows to an ORM mapped
class, since SQLAlchemy knows nothing about how your stored procedure gets
data or how that data would be modified.
If you can illustrate fully what kind of interface to the data you'd like to
see that be helpful.
Sorry for not being clear.
Instead of typing manually column names
(column1,column2,...column38 inside the query() I would like to
use previous query .keys() to list them there
Instead doing:
myresults=session.query('column1','column2','column3').from_statement
I would like to do a=session.execute(...) and then
myresults=session.query(a.keys()).from_statement() where a.keys()
returns a list of all the column names from the stored procedure, but
unfortunately passing a list like a.keys() gives me an error. If I
type it in it works fine. How can I pass in these column names ?
Should I convert a.keys() to dictionary, or some other type?
__init__
expected - got '%r' % column
sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
entity expected - got '[u'customer', u'customer_id', u'customer_num',
u'TransactionDate'..]
I tried fetchall but when I loop over the rows, I was getting a
dictionary and instead of doing row.column1, I had to use row[0].
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to run a stored procedure?
On Jul 15, 3:35 pm, Lukasz Szybalski szybal...@gmail.com wrote: On Jul 15, 2:39 pm, David Gardner dgard...@creatureshop.com wrote: take a look at:http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?... Now, Could you give me an example of it ? I understand how to run func to get values like current timestamp, or countbut how to run a stored proc? print func.assp_Report_DailyTransactions(start_date='20100701',end_date='20100715') ?? Is this the format? or? Any idea what the proper format should be? I was able to connect to the mssql database vi instance name on linux. I've updated the docs on how to setup your odbc dsn connection string: http://lucasmanual.com/mywiki/unixODBC Now that I'm connected how do I execute, pass in variables like start and end date? Thanks, Lucas -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to run a stored procedure?
On Jul 19, 10:29 am, Lukasz Szybalski szybal...@gmail.com wrote: On Jul 15, 3:35 pm, Lukasz Szybalski szybal...@gmail.com wrote: On Jul 15, 2:39 pm, David Gardner dgard...@creatureshop.com wrote: take a look at:http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?... Now, Could you give me an example of it ? I understand how to run func to get values like current timestamp, or countbut how to run a stored proc? print func.assp_Report_DailyTransactions(start_date='20100701',end_date='20100715') ?? Is this the format? or? Any idea what the proper format should be? I was able to connect to the mssql database vi instance name on linux. I've updated the docs on how to setup your odbc dsn connection string:http://lucasmanual.com/mywiki/unixODBC Now that I'm connected how do I execute, pass in variables like start and end date? Hello, I can do this: l=session.execute(assp_ReportDailyTransactions) but this returns all the data, In both pyODBC and ceODBC I can run the following which will return proper data range based on start and end date parameters. a=cursor.execute(assp_ReportDailyTransactions @start_date=?, @end_date=?,20100701,20100719) but how do I convert that to sqlalchemy like format: This gives the following error: session.execute(assp_ReportDailyTransactions @start_date=?, @end_date=?,20100701,20100719) Traceback (most recent call last): File stdin, line 1, in module File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/ SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 732, in execute engine = self.get_bind(mapper, clause=clause, **kw) File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/ SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 837, in get_bind c_mapper = mapper is not None and _class_to_mapper(mapper) or None File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/ SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/util.py, line 636, in _class_to_mapper raise exc.UnmappedClassError(class_or_mapper) sqlalchemy.orm.exc.UnmappedClassError: Class ''20100719'' is not mapped Let me know, Thanks, Lucas -- 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] how to run a stored procedure?
Hello, I need to get data out of sqlserver by running this stored procedure. From there I convert few fields and add the processed rows to a mysql table that I have created. How can I do the following in sqlalchemy? Is there a pythonic way? Assuming I already have the database connection? What would be the syntax to run a stroed procedure? And what object would the results be in? USE [Reports] GO DECLARE @return_value int EXEC@return_value = [dbo].[assp_Report_DailyTransactions] @start_date = N'07/01/2010', @end_date = N'07/15/2010' SELECT 'Return Value' = @return_value Thanks, Lucas -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to run a stored procedure?
On Jul 15, 2:39 pm, David Gardner dgard...@creatureshop.com wrote: take a look at:http://www.sqlalchemy.org/docs/reference/sqlalchemy/expressions.html?... Now, Could you give me an example of it ? I understand how to run func to get values like current timestamp, or countbut how to run a stored proc? print func.assp_Report_DailyTransactions(start_date='20100701',end_date='20100715') ?? Is this the format? or? Thanks, Lucas On 07/15/2010 12:29 PM, Lukasz Szybalski wrote: Hello, I need to get data out of sqlserver by running this stored procedure. From there I convert few fields and add the processed rows to a mysql table that I have created. How can I do the following in sqlalchemy? Is there a pythonic way? Assuming I already have the database connection? What would be the syntax to run a stroed procedure? And what object would the results be in? USE [Reports] GO DECLARE �...@return_value int EXEC @return_value = [dbo].[assp_Report_DailyTransactions] �...@start_date = N'07/01/2010', �...@end_date = N'07/15/2010' SELECT 'Return Value' = @return_value Thanks, Lucas -- David Gardner Pipeline Tools Programmer Jim Henson Creature Shop dgard...@creatureshop.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Support for IBM AS/400 database
I would check with ibm_db_sa mailing list. Maybe their example is out
of date, or there is a fix for it?
Lucas
On Wed, Feb 24, 2010 at 1:51 PM, st...@mailbag.com j...@qlf.com wrote:
Does anyone have a clue on this error? Is it due to a change in
SQLAlchemy between 0.4.0 and 0.5.8?
-Jim
On Feb 19, 5:02 pm, Jim Steil j...@qlf.com wrote:
Here is the traceback I get when following that recipe...
import sqlalchemy
from sqlalchemy import *
import ibm_db_sa.ibm_db_sa
Traceback (most recent call last):
File stdin, line 1, in module
File
c:\python26\lib\site-packages\ibm_db_sa-0.1.6-py2.6.egg\ibm_db_sa\ibm_db
_sa.py, line 24, in module
from sqlalchemy import sql, engine, schema, exceptions, logging
ImportError: cannot import name logging
The recipe is assuming SQLAlchemy 0.4.0. I'm using 0.5.8.
-Jim
On 2/19/2010 5:17 PM, Lukasz Szybalski wrote:
http://code.google.com/p/ibm-db/wiki/README
Let us know if it worked for you.
$ python
Python 2.5.1 (r251:54863, Oct 5 2007, 13:36:32)
[GCC 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)] on linux2
Type help, copyright, credits or license for more information.
import sqlalchemy
from sqlalchemy import *
import ibm_db_sa.ibm_db_sa
db2 =
sqlalchemy.create_engine('ibm_db_sa://db2inst1:sec...@host.name.com:5/pydev')
metadata = MetaData()
users = Table('users', metadata,
Column('user_id', Integer, primary_key = True),
Column('user_name', String(16), nullable = False),
Column('email_address', String(60), key='email'),
Column('password', String(20), nullable = False)
)
metadata.bind = db2
metadata.create_all()
users_table = Table('users', metadata, autoload=True, autoload_with=db2)
users_table
IBM_DB DB-API wrapper sanity test
Question is wether IBM_DB wrapper supports your as400 version. You
could try. I never found enough time to test the read functionality.
If you get it done then that would be nice as I have some data that I
need to read from there.
Thanks,
Lucas
On Fri, Feb 19, 2010 at 4:07 PM, Jim Steilj...@qlf.com wrote:
These is definitely more one DB2 driver for the AS/400. I seem to recall
seeing that there was working being done with one of them a couple years
ago, but don't recall which one. And, I don't know the status. Maybe a
better question would be... Is there anyone else out there using
SQLAlchemy
with DB2 on an AS/400?
-Jim
On 2/19/2010 3:13 PM, Michael Bayer wrote:
On Feb 18, 2010, at 10:36 AM, Jim Steil wrote:
Hi
Just wondering if there is support for the IBM AS/400 database using
SQLAlchemy.
IBM produces a DB2 driver, dont know if that overlaps with AS/400
compatibility or not.
-Jim
--
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.
--
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.
--
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.
--
OpenLdap server for User/Client Authentication in 1min.
http://lucasmanual.com/mywiki/OpenLdap#SetupOpenLdapserver.sh
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Support for IBM AS/400 database
http://code.google.com/p/ibm-db/wiki/README
Let us know if it worked for you.
$ python
Python 2.5.1 (r251:54863, Oct 5 2007, 13:36:32)
[GCC 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)] on linux2
Type help, copyright, credits or license for more information.
import sqlalchemy
from sqlalchemy import *
import ibm_db_sa.ibm_db_sa
db2 =
sqlalchemy.create_engine('ibm_db_sa://db2inst1:sec...@host.name.com:5/pydev')
metadata = MetaData()
users = Table('users', metadata,
Column('user_id', Integer, primary_key = True),
Column('user_name', String(16), nullable = False),
Column('email_address', String(60), key='email'),
Column('password', String(20), nullable = False)
)
metadata.bind = db2
metadata.create_all()
users_table = Table('users', metadata, autoload=True, autoload_with=db2)
users_table
IBM_DB DB-API wrapper sanity test
Question is wether IBM_DB wrapper supports your as400 version. You
could try. I never found enough time to test the read functionality.
If you get it done then that would be nice as I have some data that I
need to read from there.
Thanks,
Lucas
On Fri, Feb 19, 2010 at 4:07 PM, Jim Steil j...@qlf.com wrote:
These is definitely more one DB2 driver for the AS/400. I seem to recall
seeing that there was working being done with one of them a couple years
ago, but don't recall which one. And, I don't know the status. Maybe a
better question would be... Is there anyone else out there using SQLAlchemy
with DB2 on an AS/400?
-Jim
On 2/19/2010 3:13 PM, Michael Bayer wrote:
On Feb 18, 2010, at 10:36 AM, Jim Steil wrote:
Hi
Just wondering if there is support for the IBM AS/400 database using
SQLAlchemy.
IBM produces a DB2 driver, dont know if that overlaps with AS/400
compatibility or not.
-Jim
--
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.
--
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.
--
OpenLdap server for User/Client Authentication in 1min.
http://lucasmanual.com/mywiki/OpenLdap#SetupOpenLdapserver.sh
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: pass any field as a keyword param to the constructor?
I guess the proper solution is to setup your python class mapper like
this, and use the update method of the __dict__ instead of setattr.
class Recall(object):
def __init__(self, **kw):
self.__dict__.update(kw)
pass
Lucas
On Thu, Nov 26, 2009 at 3:24 PM, Lukasz Szybalski szybal...@gmail.com wrote:
Any idea how should I be set the None Type argument to str.?
x=Recall()
type(x.MAKETXT) is type 'NoneType'
set in OrmObject when it does the setattr it probably fails because
you cannot setattr on NoneType objects?
setattr(x.MAKETXT,'somevalue')
Traceback (most recent call last):
File stdin, line 1, in module
TypeError: attribute name must be string, not 'NoneType'
What is the proper way to do this?
Thanks,
Lucas
On Wed, Nov 25, 2009 at 10:36 PM, Lukasz Szybalski szybal...@gmail.com
wrote:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/GenericOrmBaseClass
class Recall(OrmObject):pass
mapper(Recall,recall_table)
record=Recall(RECORD_ID=RECORD_ID,CAMPNO=CAMPNO,MAKETXT=MAKETXT)
session.add(record)
session.flush()
This is not working if using the example set in the url. Is setattr
still working.? What is the proper way to do this.for SA
0.5.6?
SQLAlchemy 0.5 Implementation:
class OrmObject(object):
def __init__(self, **kw):
for key in kw:
if not key.startswith('_') and key in self.__dict__:
setattr(self, key, kw[key])
def __repr__(self):
attrs = []
for key in self.__dict__:
if not key.startswith('_'):
attrs.append((key, getattr(self, key)))
return self.__class__.__name__ + '(' + ', '.join(x[0] + '=' +
repr(x[1]) for x in attrs) + ')'
Thanks,
Lucas
--
Setup CalendarServer for your company.
http://lucasmanual.com/mywiki/CalendarServer
Automotive Recall Database - See if you vehicle has a recall
http://lucasmanual.com/recall
--
Setup CalendarServer for your company.
http://lucasmanual.com/mywiki/CalendarServer
Automotive Recall Database - See if you vehicle has a recall
http://lucasmanual.com/recall
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: pass any field as a keyword param to the constructor?
On Sun, Nov 29, 2009 at 3:58 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Nov 29, 2009, at 2:17 PM, Lukasz Szybalski wrote: I guess the proper solution is to setup your python class mapper like this, and use the update method of the __dict__ instead of setattr. class Recall(object): def __init__(self, **kw): self.__dict__.update(kw) pass mapper(Recall,recall_table) then. x=Recall(column1=column1.) session.add(Recall) if Recall is ORM-mapped, the above won't work. use setattr(), not __dict__ access. This seems to work in 0.5.6? Does that change in 0.6? or Yes. Lucas On Thu, Nov 26, 2009 at 3:24 PM, Lukasz Szybalski szybal...@gmail.com wrote: Any idea how should I be set the None Type argument to str.? x=Recall() type(x.MAKETXT) is type 'NoneType' set in OrmObject when it does the setattr it probably fails because you cannot setattr on NoneType objects? setattr(x.MAKETXT,'somevalue') don't you mean setattr(x, 'MAKETXT', 'somevalue') here? yesthat was my mistake... -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: pass any field as a keyword param to the constructor?
Any idea how should I be set the None Type argument to str.?
x=Recall()
type(x.MAKETXT) is type 'NoneType'
set in OrmObject when it does the setattr it probably fails because
you cannot setattr on NoneType objects?
setattr(x.MAKETXT,'somevalue')
Traceback (most recent call last):
File stdin, line 1, in module
TypeError: attribute name must be string, not 'NoneType'
What is the proper way to do this?
Thanks,
Lucas
On Wed, Nov 25, 2009 at 10:36 PM, Lukasz Szybalski szybal...@gmail.com wrote:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/GenericOrmBaseClass
class Recall(OrmObject):pass
mapper(Recall,recall_table)
record=Recall(RECORD_ID=RECORD_ID,CAMPNO=CAMPNO,MAKETXT=MAKETXT)
session.add(record)
session.flush()
This is not working if using the example set in the url. Is setattr
still working.? What is the proper way to do this.for SA
0.5.6?
SQLAlchemy 0.5 Implementation:
class OrmObject(object):
def __init__(self, **kw):
for key in kw:
if not key.startswith('_') and key in self.__dict__:
setattr(self, key, kw[key])
def __repr__(self):
attrs = []
for key in self.__dict__:
if not key.startswith('_'):
attrs.append((key, getattr(self, key)))
return self.__class__.__name__ + '(' + ', '.join(x[0] + '=' +
repr(x[1]) for x in attrs) + ')'
Thanks,
Lucas
--
Setup CalendarServer for your company.
http://lucasmanual.com/mywiki/CalendarServer
Automotive Recall Database - See if you vehicle has a recall
http://lucasmanual.com/recall
--
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] pass any field as a keyword param to the constructor?
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/GenericOrmBaseClass
class Recall(OrmObject):pass
mapper(Recall,recall_table)
record=Recall(RECORD_ID=RECORD_ID,CAMPNO=CAMPNO,MAKETXT=MAKETXT)
session.add(record)
session.flush()
This is not working if using the example set in the url. Is setattr
still working.? What is the proper way to do this.for SA
0.5.6?
SQLAlchemy 0.5 Implementation:
class OrmObject(object):
def __init__(self, **kw):
for key in kw:
if not key.startswith('_') and key in self.__dict__:
setattr(self, key, kw[key])
def __repr__(self):
attrs = []
for key in self.__dict__:
if not key.startswith('_'):
attrs.append((key, getattr(self, key)))
return self.__class__.__name__ + '(' + ', '.join(x[0] + '=' +
repr(x[1]) for x in attrs) + ')'
Thanks,
Lucas
--
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] declarative_base vs mapper
Hello,
How can I do
Index('myindex', xyz.c.type, xyz.c.name, unique=True)
in a declarative way?
class xyz(DeclarativeBase):
__tablename__ = 'xyz'
#{ Columns
type = Column(Unicode(), nullable=False)
name = Column(Unicode(), nullable=False)
how do I do index declarative style?
Thanks,
Lucas
--
Using rsync. How to setup rsyncd.
http://lucasmanual.com/mywiki/rsync
OpenLdap - From start to finish.
http://lucasmanual.com/mywiki/OpenLdap
--~--~-~--~~~---~--~~
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: declarative_base vs mapper
On Wed, Jul 29, 2009 at 4:34 PM, Lukasz Szybalskiszybal...@gmail.com wrote:
Hello,
How can I do
Index('myindex', xyz.c.type, xyz.c.name, unique=True)
in a declarative way?
class xyz(DeclarativeBase):
__tablename__ = 'xyz'
#{ Columns
type = Column(Unicode(), nullable=False)
name = Column(Unicode(), nullable=False)
__table_args__ = (ForeignKeyConstraint(...),UniqueConstraint(...),{})
Actually I needed a primary key on both fields.
type = Column(Unicode(), nullable=False, primary_key=True)
name = Column(Unicode(), nullable=False,primary_key=True , name=PK_xyz)
Now I wonder if name will name my primary key?
Do I specify auto_increment=No ?
What are the advantages of using declarative way of setting table
definitions? vs
addressbook_table = sqlalchemy.Table(Addressbook, metadata,
sqlalchemy.Column('Address_Sid', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('FirstName', sqlalchemy.Unicode(40),nullable=False),
class Addressbook(object):
def __init__(self, **kw):
automatically mapping attributes
for key, value in kw.iteritems():
setattr(self, key, value)
mapper(Addressbook, addressbook_table)
It seems to me as its harder to find information on proper syntax then
it is with regular table, py object, mapper?
Lucas
how do I do index declarative style?
Thanks,
Lucas
--
Using rsync. How to setup rsyncd.
http://lucasmanual.com/mywiki/rsync
OpenLdap - From start to finish.
http://lucasmanual.com/mywiki/OpenLdap
--
Using rsync. How to setup rsyncd.
http://lucasmanual.com/mywiki/rsync
OpenLdap - From start to finish.
http://lucasmanual.com/mywiki/OpenLdap
--~--~-~--~~~---~--~~
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] ticket_change table
Hello, I want to implement a change log table something similar to ticket_change table in trac. ticket_change table structure: Primary key (ticket, time, field) Index (ticket, time) ticket integer (fk to my table ticket) time inetger author field oldvalue newvalue How can I plugin my sqlalchemy connection / setup so that when it tries to save changes to some table (in this case ticket) it saves the old and new value for that field. Where during the sqlalchemy can I plug it in? A different example: address = Addressbook() address.FirstName = kw['FirstName'] address.LastName = kw['LastName'] DBSession.add(address) DBSession.commit() ---somewhere here for each field would get changed to my address_change table address_id time 234234 author: lucas field: FirstName oldvalue: ' ' newvalue: 'Lucas' address_id time 234234 author: lucas field: LastName oldvalue: ' ' newvalue: 'Mylastname' Ideas on how this can be done? Thanks, Lucas -- Using rsync. How to setup rsyncd. http://lucasmanual.com/mywiki/rsync OpenLdap - From start to finish. http://lucasmanual.com/mywiki/OpenLdap --~--~-~--~~~---~--~~ 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] timestamp as int
Hello,
What is the equivalent of the following but in the integer version.
Column('_last_updated', DateTime(True),
default=func.current_timestamp(),
onupdate=func.current_timestamp()),
Column('_last_updated', Integer, default=int(time.time(), onupdate=func.???),
I see that project like trac uses the integer timestamps extensively.
Can it be used safely in any other project or there are some drawbacks
for it?
Thanks,
Lucas
--
Using rsync. How to setup rsyncd.
http://lucasmanual.com/mywiki/rsync
DataHub - create a package that gets, parses, loads, visualizes data
http://lucasmanual.com/mywiki/DataHub
--~--~-~--~~~---~--~~
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: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Mon, Jun 8, 2009 at 2:46 PM, ddorothydidoro...@gmail.com wrote: I have looked into this and considered what you have said. I think I have come up with a potential solution. It seems to be that the most common driver for mssql on non-windows platforms is going to be freeTDS. Since there appears to be no way of knowing what ODBC is using under the hood we should be able to safely assume that they are using freeTDS if not on windows. Further, there should be nominal overhead in setting nocount on. So perhaps the following diff will offer what is needed to address this specific issue. --- sqlalchemy/databases/mssql.py 2009-06-01 13:00:36.0 -0400 +++ sqlalchemy/databases/mssql.py 2009-06-08 15:31:22.0 -0400 @@ -239,7 +239,7 @@ does **not** work around -import datetime, decimal, inspect, operator, re, sys, urllib +import datetime, decimal, inspect, operator, re, sys, urllib, os from sqlalchemy import sql, schema, exc, util from sqlalchemy import Table, MetaData, Column, ForeignKey, String, Integer @@ -982,6 +982,8 @@ super(MSSQLExecutionContext_pyodbc, self).pre_exec() if self.compiled.isinsert and self.HASIDENT and not self.IINSERT \ and len(self.parameters) == 1 and self.dialect.use_scope_identity: + if os.name != 'nt': + self.cursor.execute(SET NOCOUNT ON) self.statement += ; select scope_identity() def post_exec(self): @@ -996,6 +998,8 @@ except pyodbc.Error, e: self.cursor.nextset() self._last_inserted_ids = [int(row[0])] + if os.name != 'nt': + self.cursor.execute(SET NOCOUNT OFF) else: super(MSSQLExecutionContext_pyodbc, self).post_exec() Is it possible to set this nocount off somewhere on the dsn setup configuration file? Or this can only be set at run time? Thanks, Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Thu, Apr 30, 2009 at 8:36 AM, Tom Wood thomas.a.w...@gmail.com wrote: Hi Lucas, I don't think #1350 applies here, but just in case, I pass-ed out the mssql dialect do_begin per the suggestion in the discussion thread referenced by that ticket: no impact on the invalid cursor state exception. I guess at this point you would need to: 1. try doing the insert using plain pyodbc. See if pyodbc inserts the record with no problems. If pyodbc works then create a bug in sqlalchemy. If pyodbc does not work we need to find out why. Thanks, Lucas -Tom Can you read over this ticket and see if maybe you are affected.http://www.sqlalchemy.org/trac/ticket/1350 If not then somebody more familiar with sa would need to look into why these tests are failing. Thanks, Lucas -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste DataHub - create a package that gets, parses, loads, visualizes data http://lucasmanual.com/mywiki/DataHub --~--~-~--~~~---~--~~ 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: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Wed, Apr 29, 2009 at 9:08 AM, Tom Wood thomas.a.w...@gmail.com wrote:
Some additional info, and a possible fix:
I can reproduce this problem running the SQLAlchemy dialect unit
tests. Using a trunk (r5930) checkout, FreeTDS 0.82 with tds protocol
version 8.0, pyodbc 2.1.4, Python 2.5 and SQL Server 2005, I see three
test failures in dialect.mssql:
test_binary fails with:
DataError: (DataError) ('22018', '[22018] [FreeTDS][SQL Server]
Implicit conversion from data type varchar to varbinary is not
allowed. Use the CONVERT function to run this query. (257)
(SQLPrepare)') 'INSERT INTO binary_table (primary_id, data,
data_image, data_slice, misc, pickled, mypickle) VALUES
(?, ?, ?, ?, ?, ?, ?)' [1, read-only buffer for 0x842e680, size -1,
offset 0 at 0xb75c8f80, read-only buffer for 0x842e680, size -1,
offset 0 at 0xb75c8f60, read-only buffer for 0xb75e9c20, size -1,
offset 0 at 0xb75d00a0, 'binary_data_one.dat', read-only buffer for
0xb75c67a0, size -1, offset 0 at 0xb75d0180, read-only buffer for
0xb75d9d68, size -1, offset 0 at 0xb75d0100]
I'm going to ignore this for now, since it seems to be unrelated to my
problem.
However, test_fetchid_trigger and test_slice_mssql both fail with the
Invalid cursor state exception:
File /home/taw8/src/sqlalchemy-trunk/lib/sqlalchemy/engine/
base.py, line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL
Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO foo
(bar, range) VALUES (?, ?); select scope_identity()' [1, 1]
Here's a possible fix. The following patch to mssql.py corrects my
problems, as well as the test_fetchid_trigger and test_slice_mssql
failures:
Index: lib/sqlalchemy/databases/mssql.py
===
--- lib/sqlalchemy/databases/mssql.py (revision 5930)
+++ lib/sqlalchemy/databases/mssql.py (working copy)
@@ -991,7 +991,7 @@
# We may have to skip over a number of result sets with
no data (due to triggers, etc.)
while True:
try:
- row = self.cursor.fetchone()
+ row = self.cursor.fetchall()[0]
break
except pyodbc.Error, e:
self.cursor.nextset()
I.e., calling fetchall() instead of fetchone() seems to clean up the
cursor state.
Two caveats: (1) there are many other (non dialect) test failures with
and without my patch, although the patch does reduce the number. So
maybe there is something amok with my configuration. (2) I'm only
tried this on Debian--I have no idea what would happen on Windows.
Can you read over this ticket and see if maybe you are affected.
http://www.sqlalchemy.org/trac/ticket/1350
If not then somebody more familiar with sa would need to look into why
these tests are failing.
Thanks,
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Mon, Apr 27, 2009 at 10:01 AM, Tom Wood thomas.a.w...@gmail.com wrote:
Hi all,
Am having a problem with SQLAlchemy 0.5.3 and MSSQL. Running on a
Debian stack, using FreeTDS 0.82, pyodbc 2.1.4, Python 2.5 and
(separately) SQL Server 2000 and SQL Server 2005.
The (nose) test below fails with the exception:
ProgrammingError: (ProgrammingError) ('24000', '[24000] [FreeTDS][SQL
Server]Invalid cursor state (0) (SQLExecDirectW)') 'INSERT INTO
activities (institution, application_id) VALUES (?, ?); select
scope_identity()' ['UMass', 1]
Complete stack can be found below.
I'd love to hear from anyone running under a comparable configuration--
whether you see the same results or not! :-)
FYI: The test succeeds using SQLAlchemy 0.5.0rc3, but fails with every
subsequent release. It also passes running against a sqllite db.
Thanks very much.
Tom Wood
University of Connecticut
# begin test code
import sqlalchemy as sa
from sqlalchemy import orm
conn = 'mssql://insert your string here'
engine = sa.create_engine(conn)
I'm not sure if I can help but if we could start with the basics and
find out what version of tds are you using and how are you connecting?
1. Are you using dsn-less or dsn connection string? @dsn ?
2. What tds version have you set in /etc/freetds/tds.dsn.template
http://lucasmanual.com/mywiki/unixODBC
Have you tried setting it to tds version 8.0?
Thanks,
Lucas
metadata = sa.MetaData(bind=engine)
applications_table = sa.Table('applications', metadata,
sa.Column('id', sa.Integer,
primary_key=True),
sa.Column('last_name', sa.types.String
(20)))
activities_table = sa.Table('activities', metadata,
sa.Column('id', sa.Integer,
primary_key=True),
sa.Column('institution', sa.types.String
(20)),
sa.Column('application_id', sa.Integer,
sa.ForeignKey('applications.id')))
Session = orm.sessionmaker()
class Application(object):
def __init__(self, last_name):
self.last_name = last_name
class Activity(object):
def __init__(self, institution):
self.institution = institution
orm.mapper(Application, applications_table, properties={'activities':
orm.relation(Activity, backref='application')})
orm.mapper(Activity, activities_table)
class Tester(object):
def setup(self):
metadata.create_all()
self.session = Session()
def teardown(self):
self.session.close()
metadata.drop_all()
def test_orm_relation(self):
app = Application(last_name='Wood')
act = Activity(institution='UConn')
act2 = Activity(institution='UMass')
app.activities.append(act)
app.activities.append(act2)
self.session.add(app)
self.session.commit()
assert act.id is not None
assert app.id is not None
assert act2.id is not None
assert act.application_id == app.id
assert act2.application_id == app.id
# begin stack crawl
ERROR: simple_test.Tester.test_orm_relation
--
Traceback (most recent call last):
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
nose-0.10.4-py2.5.egg/nose/case.py, line 182, in runTest
self.test(*self.arg)
File /home/XXX/unicode_tests/simple_test.py, line 45, in
test_orm_relation
self.session.commit()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 673, in
commit
self.transaction.commit()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 378, in
commit
self._prepare_impl()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 362, in
_prepare_impl
self.session.flush()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 1351, in
flush
self._flush(objects)
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/session.py, line 1422, in
_flush
flush_context.execute()
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 244, in
execute
UOWExecutor().execute(self, tasks)
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 707, in
execute
self.execute_save_steps(trans, task)
File /home/XXX/virtual_envs/py25-pylons/lib/python2.5/site-packages/
SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/orm/unitofwork.py, line 722, in
execute_save_steps
self.save_objects(trans, task)
File
[sqlalchemy] Re: 0.5.2 seems to always roll back with mssql
On Mar 25, 2:14 pm, Rick Morrison rickmorri...@gmail.com wrote: Yep, same here. ..on my mssql 2005, I tried this query batch: set implicit_transactions on go select 'After implicit ON', @@trancount exec sp_datatype_info go select 'After query w/implicit', @@trancount begin transaction go select 'After BEGIN', @@trancount Here's the output: - After implicit ON 0 After query w/implicit 1 After BEGIN 2 Our support team also found that calling commit() after the connect also worked. I guess this will close the outer transaction. It's a bit of a hack, but it sounds like a simple 2-cent solution. We could issue something like: IF @@TRANCOUNT 0 COMMIT on connection establishment. Joined in kind of late on thisbut, what version of TDS are you setting in your dsn setup? (Not sure if that makes a difference or not) Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: 0.5.3 and mssql
On Wed, Apr 8, 2009 at 11:36 PM, mdoar md...@pobox.com wrote:
This is what's working for me with 0.5.3
raw_cs = SERVER=%s;DATABASE=%s;UID=%s;PWD=%s % (server, database,
userid, password)
connection_string = %s:///?odbc_connect=%s % (databasetype,
urllib.quote_plus(raw_cs))
if databasetype in ['mssql']:
connection_string += urllib.quote_plus(;DRIVER={SQL
Server};TDS_Version=8.0)
echoOn = True # For debugging the SQL statements
engine = create_engine(connection_string, echo=echoOn)
What version of pyodbc are you using?
Thanks,
Lucas
If TDSVER is not seen and the default version is used, you may get
SystemError: 'finally' pops bad exception
Now that said I have also had to force the version with
./configure --with-tdsver=8.0
make
sudo make install
or
export TDSVER=8.0
or if you can't redeploy freetds copy freetds.conf to ~/.freetds.conf
and change the version in that file to 8.0
FreeTS, ODBC, pyODBC and then SQLAlchemy: what a house of cards!
~Matt
On Apr 7, 12:58 pm, Lukasz Szybalski szybal...@gmail.com wrote:
Hello,
Is this still proper connection string for mssql where I specify the
tds version and a connection driver name?
e =
sqlalchemy.create_engine(mssql://user:p...@server:1433/db_name?driver=TDSodbc_options='TDS_Version=8.0')
What is the syntax for driver, and how can I pass TDS_Version=8.0 to
the end of connection string?
Thanks,
Lucas
File
/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/pool.py,
line 198, in __init__
self.connection = self.__connect()
File
/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/pool.py,
line 261, in __connect
connection = self.__pool._creator()
File
/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/strategies.py,
line 80, in connect
raise exc.DBAPIError.instance(None, None, e)
sqlalchemy.exc.DBAPIError: (Error) ('08001', '[08001]
[unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0)
(SQLDriverConnectW)') None None
--
How to create python package?http://lucasmanual.com/mywiki/PythonPaste
DataHub - create a package that gets, parses, loads, visualizes
datahttp://lucasmanual.com/mywiki/DataHub
--
How to create python package?
http://lucasmanual.com/mywiki/PythonPaste
DataHub - create a package that gets, parses, loads, visualizes data
http://lucasmanual.com/mywiki/DataHub
--~--~-~--~~~---~--~~
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: 0.5.3 and mssql
On Tue, Apr 7, 2009 at 2:58 PM, Lukasz Szybalski szybal...@gmail.com wrote:
Hello,
Is this still proper connection string for mssql where I specify the
tds version and a connection driver name?
e =
sqlalchemy.create_engine(mssql://user:p...@server:1433/db_name?driver=TDSodbc_options='TDS_Version=8.0')
1. Just to let you know... the dsn connection works as long as
TDS_Version = 8.0 is provided in the dsn settings of freetds for
mssql 2000.
2. I went over your sampeles at :
http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/databases/mssql.py
and using the mssql:///?odbc_connect=dsn%3Dmydsn%3BDatabase%3Ddb
I was able to get proper dns string.
The syntax is weird because the code needs to look like this and needs
to use %s to substitute the string. I don't know if you run into
unicode problems or escaping issues. Is there a better way to do this?
import urllib
url=
urllib.quote_plus('DRIVER={TDS};Server=servername;Database=dbname;UID=user;PWD=pass;port=123;TDS_Version=8.0;')
e = sqlalchemy.create_engine(mssql:///?odbc_connect=%s % url)
I wish you guys did this in a code so I could only provide something like this:
e =
sqlalchemy.create_engine(mssql:///?odbc_connect='DRIVER={TDS};Server=servername;Database=dbname;UID=user;PWD=pass;port=123;TDS_Version=8.0;'')
Notice the single quotes after odbc_connect='
If not could you copy above 3 lines of code I've put as an example and
add it to your docs for mssql. I would never know how to get here
without being subscribed to this list and know about the changes and
know to look at the source code file.
3. With dsn-less connection there seems to be some kind of issue with
pyodbc, but I can't figure out what the problem is.
import pyodbc
pyodbc.connect('DRIVER={TDS};Server=servername;Database=dbname;UID=user;PWD=pass;port=123;')
Traceback (most recent call last):
File stdin, line 1, in module
pyodbc.Error: ('08S01', '[08S01] [unixODBC][FreeTDS][SQL Server]Unable
to connect: Adaptive Server is unavailable or does not exist (20009)
(SQLDriverConnectW)')
Anybody might know how to solve this?
Thanks,
Lucas
What is the syntax for driver, and how can I pass TDS_Version=8.0 to
the end of connection string?
Thanks,
Lucas
File
/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/pool.py,
line 198, in __init__
self.connection = self.__connect()
File
/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/pool.py,
line 261, in __connect
connection = self.__pool._creator()
File
/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/strategies.py,
line 80, in connect
raise exc.DBAPIError.instance(None, None, e)
sqlalchemy.exc.DBAPIError: (Error) ('08001', '[08001]
[unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0)
(SQLDriverConnectW)') None None
--
How to create python package?
http://lucasmanual.com/mywiki/PythonPaste
DataHub - create a package that gets, parses, loads, visualizes data
http://lucasmanual.com/mywiki/DataHub
--
How to create python package?
http://lucasmanual.com/mywiki/PythonPaste
DataHub - create a package that gets, parses, loads, visualizes data
http://lucasmanual.com/mywiki/DataHub
--~--~-~--~~~---~--~~
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] 0.5.3 and mssql
Hello,
Is this still proper connection string for mssql where I specify the
tds version and a connection driver name?
e =
sqlalchemy.create_engine(mssql://user:p...@server:1433/db_name?driver=TDSodbc_options='TDS_Version=8.0')
What is the syntax for driver, and how can I pass TDS_Version=8.0 to
the end of connection string?
Thanks,
Lucas
File
/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/pool.py,
line 198, in __init__
self.connection = self.__connect()
File
/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/pool.py,
line 261, in __connect
connection = self.__pool._creator()
File
/home/lucas/tmp/saENV/lib/python2.5/site-packages/SQLAlchemy-0.5.3-py2.5.egg/sqlalchemy/engine/strategies.py,
line 80, in connect
raise exc.DBAPIError.instance(None, None, e)
sqlalchemy.exc.DBAPIError: (Error) ('08001', '[08001]
[unixODBC][FreeTDS][SQL Server]Unable to connect to data source (0)
(SQLDriverConnectW)') None None
--
How to create python package?
http://lucasmanual.com/mywiki/PythonPaste
DataHub - create a package that gets, parses, loads, visualizes data
http://lucasmanual.com/mywiki/DataHub
--~--~-~--~~~---~--~~
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] resultset print keys values
Hello, Could somebody tell me how can I print the object data in my result set without knowing the column names? myresult=session.query(...).all() for i in myresult: print I need to debug some data and its hard to print the object keys and values (column names and its values) . i.keys() ? i.items()? some dictionary like functions would be nice. sqlalchemy.__version__ '0.5.0rc1' Thanks, Lucas -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste Bazaar and Launchpad http://lucasmanual.com/mywiki/Bazaar --~--~-~--~~~---~--~~ 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: resultset print keys values
On Tue, Feb 10, 2009 at 1:32 PM, a...@svilendobrev.com wrote: object-query or plain query? - objects are .. whatever class it is; print the i.__dict__ or str(i) or whatever - plain-sql-query ones are RowProxy, they have i.keys() i.items() i.values() i.__dict__ it is... Thanks a lot... Lucas On Tuesday 10 February 2009 21:27:09 Lukasz Szybalski wrote: Hello, Could somebody tell me how can I print the object data in my result set without knowing the column names? myresult=session.query(...).all() for i in myresult: print I need to debug some data and its hard to print the object keys and values (column names and its values) . i.keys() ? i.items()? some dictionary like functions would be nice. -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste Bazaar and Launchpad http://lucasmanual.com/mywiki/Bazaar --~--~-~--~~~---~--~~ 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: resultset print keys values
On Tue, Feb 10, 2009 at 1:52 PM, Michael Bayer mike...@zzzcomputing.com wrote: dir(instance) is preferable to __dict__.keys() - the latter will not give you deferred attributes, unloaded collections, or the expired version of each of those. dir() respects descriptors basically. but then dir() includes stuff like: '__class__', '__delattr__', '__dict__', '__doc__', '__getattribute__', '__hash__', '__init__', '__module__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__str__', '__weakref__', '_sa_class_manager', '_sa_instance_state'] Which need to be filtered. Then for the remaining items need to loop through to get the value?! I figured there was a uniform function that would return dictionary of key/value pairs that is available on all the possible return objects. __dict__ is good enough for visual inspection for now. Thanks, Lucas Lukasz Szybalski wrote: Hello, Could somebody tell me how can I print the object data in my result set without knowing the column names? myresult=session.query(...).all() for i in myresult: print I need to debug some data and its hard to print the object keys and values (column names and its values) . i.keys() ? i.items()? some dictionary like functions would be nice. sqlalchemy.__version__ '0.5.0rc1' Thanks, Lucas -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste Bazaar and Launchpad http://lucasmanual.com/mywiki/Bazaar -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste Bazaar and Launchpad http://lucasmanual.com/mywiki/Bazaar --~--~-~--~~~---~--~~ 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] Announcing Datahub 0.7
http://lucasmanual.com/mywiki/DataHub *Datahub is a tool that allows faster download/crawl, parse, load, and visualize of data. It achieves this by allowing you to divide each step into its own work folders. In each work folder you get a sample files that you can start coding. *Datahub is for people who found some interesting data source for them, they want to download it, parse it, load it into database, provide some documentation, and visualize it. Datahub will speed up the process by creating folder for each of these actions. You will create all the programs from our base default template and move on to analyzing the data in no time. How to get started?: Datahub is a python based tool and here is how to run it. **Create python virtualenviroment: virtualenv --no-site-packages datahubENV source datahubENV/bin/activate **How to get it: wget http://launchpad.net/datahub/trunk/0.7/+download/datahub-0.7.tar.gz tar -xzvf datahub-0.7.tar.gz ** Install it: cd datahub-0.7/ python setup.py install **Create you project using datahub default templates: paster create --list-templates paster create -t datahub ** Where do I start: Above commands created a project skeleton that has 4 folders: crawl (sample code to download via wget or harvestman), parse (here is where you parse raw data), load (here is where you load the data into database using sqlalchemy or a tool of your choice), hdf5 (convert to hdf5 if you don't want to use database), wiki (provide some documentation) This is a first release, so feedback is appreciated. Give it a try if you have some interesting data to deal with. Thanks, Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Error Connecting to MSSQL using pyodbc and SqlAlchemy 0.5.0
On Fri, Jan 9, 2009 at 4:18 PM, TJ Ninneman t...@twopeasinabucket.com wrote:
On Jan 9, 2009, at 3:58 PM, TJ Ninneman wrote:
Since I've been having troubles with pymssql and 0.5.0 I thought I
would try pyodbc on my Mac.
I'm able to connect and execute sql using isql without issue:
isql -v MyDbODBC username password
But when I connect through SqlAlchemy using:
sqlalchemy.url = mssql://username:password@/?dsn=MyDbODBC
I get:
DBAPIError: (Error) ('0', '[0] [iODBC][Driver
Manager]dlopen(FreeTDS, 6): image not found (0) (SQLDriverConnectW)')
None None
Any Ideas? What exactly does Image not found mean?
Thanks!
TJ
I run this in the context of Pylons...a sqlalchemy only example:
import sqlalchemy
engine = sqlalchemy.create_engine('mssql://username:password@/?
dsn=MyDbODBC')
con = engine.connect()
Traceback (most recent call last):
File stdin, line 1, in module
File /Users/tninneman/Development/twopeas/lib/python2.5/site-
packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/engine/base.py, line
1231, in connect
return Connection(self, **kwargs)
File /Users/tninneman/Development/twopeas/lib/python2.5/site-
packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/engine/base.py, line
538, in __init__
self.__connection = connection or engine.raw_connection()
File /Users/tninneman/Development/twopeas/lib/python2.5/site-
packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/engine/base.py, line
1286, in raw_connection
return self.pool.unique_connection()
File /Users/tninneman/Development/twopeas/lib/python2.5/site-
packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/pool.py, line 142, in
unique_connection
return _ConnectionFairy(self).checkout()
File /Users/tninneman/Development/twopeas/lib/python2.5/site-
packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/pool.py, line 323, in
__init__
rec = self._connection_record = pool.get()
File /Users/tninneman/Development/twopeas/lib/python2.5/site-
packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/pool.py, line 180, in
get
return self.do_get()
File /Users/tninneman/Development/twopeas/lib/python2.5/site-
packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/pool.py, line 615, in
do_get
con = self.create_connection()
File /Users/tninneman/Development/twopeas/lib/python2.5/site-
packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/pool.py, line 145, in
create_connection
return _ConnectionRecord(self)
File /Users/tninneman/Development/twopeas/lib/python2.5/site-
packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/pool.py, line 217, in
__init__
self.connection = self.__connect()
File /Users/tninneman/Development/twopeas/lib/python2.5/site-
packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/pool.py, line 280, in
__connect
connection = self.__pool._creator()
File /Users/tninneman/Development/twopeas/lib/python2.5/site-
packages/SQLAlchemy-0.4.8-py2.5.egg/sqlalchemy/engine/strategies.py,
line 80, in connect
raise exceptions.DBAPIError.instance(None, None, e)
sqlalchemy.exceptions.DBAPIError: (Error) ('0', '[0] [iODBC]
[Driver Manager]dlopen(FreeTDS, 6): image not found (0)
(SQLDriverConnectW)') None None
What is mac using to connect to mssql? For example on unix there is a
freetds (free version of ms driver for sql server) and unixodbc. If
mac is using the same thing then you can try:
http://lucasmanual.com/mywiki/PythonManual#head-0e30402e75b2d4a8a032e6830813b779a34636e3
aka.
install unixodbc, crate driver name and connect using the connection
string mentioned there.
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Error Connecting to MSSQL using pyodbc and SqlAlchemy 0.5.0
On Fri, Jan 9, 2009 at 4:46 PM, Rick Morrison rickmorri...@gmail.com wrote: The MSSQL connection string changed for the 0.5 final release. In particular, the dsn keyword is removed, and the pyodbc connection string now expects the DSN to be named where the host was previously placed, so the new connection URL would be: mssql://username:passw...@mydbodbc For fine-grained control over pyodbc connections, a new odbc_connect keyword allows a full ODBC connection string to be specified. See the mssql module doc comments for details. ok. I'll test it out on monday.. http://www.sqlalchemy.org/docs/05/reference/dialects/mssql.html#connecting ps. I miss the one page documentation(easier to search) , is that available or can be done with sphinx? Thanks, Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL default_schema
On Tue, Dec 23, 2008 at 3:34 PM, Randall Smith rand...@tnr.cc wrote: Michael Bayer wrote: so if we can confirm this ! .socloseto...release From grepping: For the Oracle dialect get_default_schema_name is used in reflecttable, has_table and has_sequence. has_table, in turn, is used by the SchemaGenerator and SchemaDropper. MSSQL uses it for reflecttable and has_table and others do the same or less. Outside of the dialects, get_default_schema_name is used for Engine.table_names If you guys tell me how, I could run the unit test for sqlalchemy on 2005, and 2000 sql server. Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] mssql unit tests how?
Hello, During 0.4.6 I was getting some help on mssql via linux, and it was expressed to me that it would be nice to run unit tests of sqlalchemy on mssql. I've just got new sql server 2005 installed if you guys tell me exactly (copy paste) what do I need to do to run these tests, I will run them. Let me know. Lucas -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste Bazaar and Launchpad http://lucasmanual.com/mywiki/Bazaar --~--~-~--~~~---~--~~ 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: MSSQL pyodbc connection string - broken for me!
On Wed, Dec 10, 2008 at 1:49 PM, desmaj [EMAIL PROTECTED] wrote:
On Dec 10, 1:27 pm, Rick Morrison [EMAIL PROTECTED] wrote:
This has been bandied back and forth for months, and I think it's becoming
clear that having sqla map dburl's to ODBC connection strings is a losing
battle. Yet another connection argument is not sounding very attractive to
me.
Perhaps a simple reductionist policy for ODBC connections would be best. The
user would either specify a DSN, which would be passed to the driver, or
give a full ODBC connection string (presumably via a keyword arg) which
would be used verbatim. Anything else seems to degrade to the kind of
error-prone heuristics we see here.
You make a good point about heuristics. That describes the current
implementation as well as my initial proposal involving argument
values of 'windows' and 'freetds' and stuff. I'm glad you pointed that
out. For the reasons I'll add below, I still favor a connection
argument, but the argument values should indicate the outcome and not
try to attribute behaviors to platforms when there's a lack of full
understanding about what those behaviors are. I still can't think of
good names.
I dislike the idea of relying heavily on DSNs since I don't want SA to
tell people how to manage their systems. Giving full support to DSN-
less connections let's SA work with existing systems.
Depending on a keyword argument seems troublesome, too, since there
are so many existing configurations using SA that count on being able
to specify connection information through a dburl. sqlalchemy-migrate
is the one that I'm working with now. I may be overstating this, but
the sense that I have is that using keyword arguments to specify
connection information is something that most people don't do. I do it
all the time so I'm glad it's there, but it's always felt like a
workaround.
It is a big change from the current behavior of trying to fit in with the
way that the dburl works for other dialects, though. Jason's dialect
refactor is going to confront this problem head-on as well. Any thoughts
regarding this from that perspective?
I don't know what this wll look like, so maybe the discussion is moot.
I look forward to hearing about it.
I've been using the following connection string with unixodbc for a
year now since 0.4.6 version.
sqlalchemy.create_engine(mssql://user:[EMAIL
PROTECTED]:1433/databasename?driver=TDSodbc_options='TDS_Version=8.0')
which gets translated into :
pyodbc.connect(SERVER=;UID=xxx;PWD=xxx;DRIVER={TDS};TDS_Version=7.0)
It works great for me.
So what is not working exactly? Can you provide an example:
Have you setup the unixodbc/freetds already?
http://lucasmanual.com/mywiki/unixODBC
Thanks,
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL pyodbc connection string - broken for me!
On Wed, Dec 10, 2008 at 2:05 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Dec 10, 2008, at 2:49 PM, desmaj wrote: I dislike the idea of relying heavily on DSNs since I don't want SA to tell people how to manage their systems. Giving full support to DSN- less connections let's SA work with existing systems. DSNs would eliminate these issues for SQLAlchemy. DSNs are the recommended way to connect with ODBC and im always confused why people don't use them. Back when I used to use ODBC heavily, it was the *only* way to connect. All of this host/port stuff with ODBC client libraries is unknown to me. this is just my 2c, im not here to say how it should be done or not. I would think that the standard SQLA host/port connect pattern should work as well if we just are aware of what kind of client library we're talking to. If we can't autodetect that, then we just use a keyword argument ?connect_type=FreeTDS. We can document all the different connect types somewhere and just adapt to all the newcomers that way. With DSN being the default. I definitely do not want to start allowing raw connect strings through create_engine() - if you need to do that, use the creator() function. Whats the status of 0.5, is DSN the default in trunk now ? In my opinion sqlalchemy should provide dsn and dsn-less connections options. I know it does dns-less and I'm not sure what the status is on dsn connections. If both are available I think that is great, if you force users to use one or another that would cause problems for me especially if you don't allow dns-less connections. Thanks, Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL pyodbc connection string - broken for me!
On Wed, Dec 10, 2008 at 3:42 PM, Rick Morrison [EMAIL PROTECTED] wrote:
Whats the status of 0.5, is DSN the default in trunk now ?
DSN is the first choice in MSSQLDialect_pyodbc.make_connect_string
right now.
That's not what I see. I just pulled the 0.5 trunk, which I haven't been
tracking lately. Still uses the 'dsn' keyword build a connection string with
DSN, otherwise defaults to the former dsn-less connection string behavior.
What Mike is taking about is an idea initially advanced by Marc-Andre
Lemburg to make the 'host' portion of the dburl to represent a DSN for
pyodbc connections (any supplied database name would be ignored) for the 0.5
trunk, and have the existing dsn-less connection behavior become a
keyword-only kind of thing.
I made a patch for that many moons ago, but never committed it; it makes a
lot of sense, and the 0.5 release would be an appropriate time to introduce
this kind of compatibilty-breaking behavior.
Rick, Michael could you guys give me an example of the connection string then?
If I understand this correctly:
You guys are saying that dsn=... would replace the host:port or
host,port,databasename?
Would this change allow to supply odbc options just like I can
currently with TDS_version=7.0 which can only be supplied at a
connection?
http://www.freetds.org/userguide/odbcconnattr.htm
I'm not clear as far as what functionality you guys want to break:
From pyodbc docs the following are the connection strings so it would
seem to me that these could be fairly easy to create based on dsn= or
not in a sqlalchemy connection string. Unless I'm way of topic then
just let me know.
cnxn = pyodbc.connect(DSN=dsnname;UID=user;PWD=password)
or
cnxn = pyodbc.connect('DRIVER={SQL
Server};SERVER=server;DATABASE=database;UID=user;PWD=password)
Thanks,
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] from sqlalchemy all() to subquery to threaded process?
Hello, I am pulling a list of files I need to print from a database and I have all the records after doing the sqlalchemy query with .all() Now I have a list of 3000 files I need to print, around 3 files per userID, I want to print all 3 at the same time in different threads, and my only requirement is that all files are printed for user 1 before we start printing for user 2. Because the process doesn't use full power of the CPU when I print one by one, I want to thread the process so it prints all 3 files at the same time in different threads. When its done it moves on to the next userid. How can I sub query by user Id? results= Session.query(PrintTable).filter(PrintTable.Date='20081120').order_by(PrintTable.Username).all() How can I from above result go to: 1. In a for loop, a. get a list of files for userid=1 (userid=1 files to print file1,file2,file3) b. pass the 3 filenames to a thread function that will print the files c. go to the next userid Any idea how can this be done? I can find some tutorials on threading but I'm not sure how can I subquery to get the 3 file names? Thanks, Lucas -- Turbogears2 Manual http://lucasmanual.com/mywiki/TurboGears2 Bazaar and Launchpad http://lucasmanual.com/mywiki/Bazaar --~--~-~--~~~---~--~~ 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 count group_by where ?
On Sat, Oct 11, 2008 at 10:24 PM, Michael Bayer [EMAIL PROTECTED] wrote: We should really fix the MSSQL dialect to not be throwing a SystemError when a SQL statement is not successfully interpreted. I guess syntax error would be more appropriate. I can't see anything wrong with the statement otherwise unless MSSQL has a problem with the same column being in GROUP BY as well as an aggregate. I'd get it to work at the SQL prompt first. The list [ ] in a group_by function has caused the error. Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: select count group_by where ?
On Fri, Oct 10, 2008 at 10:32 AM, Simon [EMAIL PROTECTED] wrote:
The statement looks good in my book...what does system error mean
exactly?
On 10 Okt., 17:15, Lukasz Szybalski [EMAIL PROTECTED] wrote:
Hello,
Could anybody tell me what is wrong with this select statement?
Records is a mapper.
group=sqlalchemy.select([Records.TRANS_TYPE,Records.TR_DATE,func.count(Records.TR_DATE).label('date_count')],
Records.TRANS_TYPE==29).group_by([Records.TRANS_TYPE,Records.TR_DATE]).execute().fetchall()
I get system error..
Ideas?
I can't find a full example that works nowhere on the doc pages.
Thanks,
Lucas
--
Did I do the where statement correctly?
Lucas
here is the error.//.
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/sql/expression.py,
line 1108, in execute
return e.execute_clauseelement(self, multiparams, params)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py,
line 1227, in execute_clauseelement
return connection.execute_clauseelement(elem, multiparams, params)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py,
line 899, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py,
line 911, in _execute_compiled
self.__execute_raw(context)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py,
line 920, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py,
line 962, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context=context)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/databases/mssql.py,
line 814, in do_execute
super(MSSQLDialect_pyodbc, self).do_execute(cursor, statement,
parameters, context=context, **kwargs)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/databases/mssql.py,
line 490, in do_execute
cursor.execute(SET IDENTITY_INSERT %s OFF %
self.identifier_preparer.format_table(context.compiled.statement.table))
SystemError: 'finally' pops bad exception
--~--~-~--~~~---~--~~
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 count group_by where ?
group_by does not take a list but all individual columns as parameters
(so just omit those []s).
has fixed the error.
I think the newer version of sqlachemy no longer has Record.c its just
Record.somefield...
What also confused me was the fact that in order to do where zyx =2 in
query you issue filter() and in select you don't have filter()
function and you enter your where stuff inside of select.
It works now.
Thanks,
Lucas
On Fri, Oct 10, 2008 at 11:46 AM, Simon [EMAIL PROTECTED] wrote:
Actually, if TRANS_TYPE is a mapped attribute of the RecordClass class
(or whatever its name is), it should be Records.c.TRANS_TYPE. Also,
group_by does not take a list but all individual columns as parameters
(so just omit those []s).
On 10 Okt., 17:38, Lukasz Szybalski [EMAIL PROTECTED] wrote:
On Fri, Oct 10, 2008 at 10:32 AM, Simon [EMAIL PROTECTED] wrote:
The statement looks good in my book...what does system error mean
exactly?
On 10 Okt., 17:15, Lukasz Szybalski [EMAIL PROTECTED] wrote:
Hello,
Could anybody tell me what is wrong with this select statement?
Records is a mapper.
group=sqlalchemy.select([Records.TRANS_TYPE,Records.TR_DATE,func.count(Records.TR_DATE).label('date_count')],
Records.TRANS_TYPE==29).group_by([Records.TRANS_TYPE,Records.TR_DATE]).execute().fetchall()
I get system error..
Ideas?
I can't find a full example that works nowhere on the doc pages.
Thanks,
Lucas
--
Did I do the where statement correctly?
Lucas
here is the error.//.
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/sql/expression.py,
line 1108, in execute
return e.execute_clauseelement(self, multiparams, params)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py,
line 1227, in execute_clauseelement
return connection.execute_clauseelement(elem, multiparams, params)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py,
line 899, in execute_clauseelement
return self._execute_compiled(elem.compile(dialect=self.dialect,
column_keys=keys, inline=len(params) 1), distilled_params=params)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py,
line 911, in _execute_compiled
self.__execute_raw(context)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py,
line 920, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/engine/base.py,
line 962, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context=context)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/databases/mssql.py,
line 814, in do_execute
super(MSSQLDialect_pyodbc, self).do_execute(cursor, statement,
parameters, context=context, **kwargs)
File
/usr/local/pythonenv/BASELINE2/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc1-py2.4.egg/sqlalchemy/databases/mssql.py,
line 490, in do_execute
cursor.execute(SET IDENTITY_INSERT %s OFF %
self.identifier_preparer.format_table(context.compiled.statement.table))
SystemError: 'finally' pops bad exception
--
Python and OpenOffice documents and templates
http://lucasmanual.com/mywiki/OpenOffice
Fast and Easy Backup solution with Bacula
http://lucasmanual.com/mywiki/Bacula
--~--~-~--~~~---~--~~
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: auto incrementing -serializable isolation
On Fri, Oct 10, 2008 at 2:06 AM, [EMAIL PROTECTED] wrote: hi there. i had similar thread some months ago http://groups.google.com/group/sqlalchemy/browse_thread/thread/1a3790d58e819f01/4d8e175fc04055aa the option 2 has 2 subcases. - one is to keep all numberings in one table and have a somewhat twisted query+update mechanism (Sequence if u have it, or manual +locks otherwise) - we had that before; - another one that i invented is to have a separate table for each kind of numbering, and each number is a reference to a record in that table. This needs no locking, querying and does not rely on Sequence or similar DB-query-and-update mechanims. it also can work on any scheme for numbering - the number itself can be anything, e.g. text, or list dept2-room3-pos7. but i guess can be somewhat too verbose. On Thursday 09 October 2008 23:14:27 Lukasz Szybalski wrote: Hello, I was wondering if anybody has a good strategy for auto incrementing fields. I want to auto increment field called case# . I have a choice of database auto increment on field case# or do it myself? (correct? No other choices exists? or something in between?) 1. I would like to be able to do pick a number where we will start doing a case#? 2. Reserver a case# for a special group which can auto increment case# between 2,000,000-2,999,999, and add them as they come. 3. I don't want to use (system_id) So it seems as the only way is to make my primary key: case# - unique key, primary, not auto incrementing and let some program manage auto incrementing. What options do I have with sqlalchemy to manage any range of these primary keys? 1. let db auto increment 2. Hold the next case# in a separate database table, and let my program use it to find next case# value. How would I lock/unlock the next case# to make there is no race condition and each case# is taken/successfully saved. 3. Any other options? Have people exeperienced with other strategy that is semi-automatic, and would for for these cases.? It seems as a good solution will be to take the next case# to a different table and use that with isolation/serialization so there is no deadlocks in the application. For this thread (http://groups.google.com/group/sqlalchemy-devel/browse_thread/thread/653b6b2f49a940b8) it seems as postgreSQL is a way to go, compared to mssql for example. Am I missing anything/other options ? Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mysql to sqlalchemy table definition in .py file?
On Fri, Oct 10, 2008 at 1:50 AM, [EMAIL PROTECTED] wrote: On Friday 10 October 2008 02:06:51 Lukasz Szybalski wrote: On Tue, Sep 30, 2008 at 2:35 AM, [EMAIL PROTECTED] wrote: well it's up to you to extend it to mysql... i don't use mysql, nor i know much about sql anyway; all specific stuff there is a steal/copy/try/error. http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc /metadata/autoload.py?revision=208view=markup give it dburl Does this code actually prints the table structure in a sqlalchemy way? or just moves the data from one db to another? does print. have u tried it? moving is in the other files. python autoload.py mysql://user:[EMAIL PROTECTED]/production Traceback (most recent call last): File autoload.py, line 203, in ? assert 0, 'unsupported engine.dialect:'+str( engine.dialect) AssertionError: unsupported engine.dialect:sqlalchemy.databases.mysql.MySQLDialect object at 0xb782a02c but I found sqlaclhemy-migrate which did the job. http://code.google.com/p/sqlalchemy-migrate/wiki/MigrateVersioning And the code is: easy_install sqlalchemy-migrate migrate create_model --url=mysql://user:[EMAIL PROTECTED]/production --repository=some model.py I can actually use this to manage the db conversion from now on. Thanks, Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] auto incrementing
Hello, I was wondering if anybody has a good strategy for auto incrementing fields. I want to auto increment field called case# . I have a choice of database auto increment on field case# or do it myself? (correct? No other choices exists? or something in between?) 1. I would like to be able to do pick a number where we will start doing a case#? 2. Reserver a case# for a special group which can auto increment case# between 2,000,000-2,999,999, and add them as they come. 3. I don't want to use (system_id) So it seems as the only way is to make my primary key: case# - unique key, primary, not auto incrementing and let some program manage auto incrementing. What options do I have with sqlalchemy to manage any range of these primary keys? 1. let db auto increment 2. Hold the next case# in a separate database table, and let my program use it to find next case# value. How would I lock/unlock the next case# to make there is no race condition and each case# is taken/successfully saved. 3. Any other options? Have people exeperienced with other strategy that is semi-automatic, and would for for these cases.? Thanks, Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mysql to sqlalchemy table definition in .py file?
On Tue, Sep 30, 2008 at 2:35 AM, [EMAIL PROTECTED] wrote: well it's up to you to extend it to mysql... i don't use mysql, nor i know much about sql anyway; all specific stuff there is a steal/copy/try/error. http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/metadata/autoload.py?revision=208view=markup give it dburl Does this code actually prints the table structure in a sqlalchemy way? or just moves the data from one db to another? Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] mysql to sqlalchemy table definition in .py file?
Hello, I've asked this about 6 months ago and I was wondering if there was any progress I have existing mysql database, and I would like to connect to it via sqlalchemy using autoload and generate a python file style schema out of it. I want to migrate my existing mysql database to a mydb.py file which I can then use as my base for further development of the database. The last time I checked there were program to move from one database to another but it didn't support mysql. Thanks, Lucas -- Python and OpenOffice documents and templates http://lucasmanual.com/mywiki/OpenOffice Fast and Easy Backup solution with Bacula http://lucasmanual.com/mywiki/Bacula --~--~-~--~~~---~--~~ 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] from as400 via sqlalchemy to ?
Hello, A while back somebody mentioned they were able to connect to as400. Are there any instructions on how to do that? Could you email them to me. My final goal is to move the data from as400 to mysql or postgresql, while keeping the table layout, and hopefully keys. Any info on what needs to happen before I could do that? Thanks, Lucas -- Python and OpenOffice documents and templates http://lucasmanual.com/mywiki/OpenOffice Fast and Easy Backup solution with Bacula http://lucasmanual.com/mywiki/Bacula --~--~-~--~~~---~--~~ 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] csv engine?
Hello, I was wondering if there are any plans to have a csv engine for sqlalchemy. I would like to see support for csv. There are some cases where csv is the best way to convert data to and from especially when they require cleaning. What I would like to see is a sqlalchemy wrapping over csv module and providing file definition and some of the sql functionality into csv world. This would be really helpful when moving things over from one system to another. Ideas? Thanks, Lucas -- OpenOffice and Python http://lucasmanual.com/mywiki/OpenOffice Commercial Grade Backup with Bacula http://lucasmanual.com/mywiki/Bacula --~--~-~--~~~---~--~~ 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] SqlAchemy and OpenOffice
Hello, I have created a manual and give example on how to use sqlalchemy and openoffice together to create documents. The code shows you how to use openoffice as a document template and fill in the data from the database. I basically use Find and Replace function of openoffice to do the template work. SqlAlchemy and OpenOffice http://lucasmanual.com/mywiki/OpenOffice#head-a6f86c6beea8170c083cd3f45438066e6f20760f The program works great. I'm able to create around 100 letters per minute which is way better then typing them. Because it is so easy to find and replace in openoffice I can let marketing people design the forms, and I just fill it in. Great Combination!!! Enjoy, Lucas -- OpenOffice and Python http://lucasmanual.com/mywiki/OpenOffice Commercial Grade Backup with Bacula http://lucasmanual.com/mywiki/Bacula --~--~-~--~~~---~--~~ 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: Connecting to MySQL
On Thu, Jul 10, 2008 at 8:20 AM, Heston James
[EMAIL PROTECTED] wrote:
Good afternoon guys,
I'm brand new to SQLAlchemy as of about 2 minutes ago, I have a couple
of starter questions that I wanted to run past you, with any luck
they'll be simple for you to answer.
After looking through the quick tutorial, which showed me just about
all I need to know http://www.sqlalchemy.org/docs/05/ormtutorial.html
it talks about connecting to an in memory sqllite database using a
connection string like so:
create_engine('sqlite:///:memory:', echo=True)
Here is a list of possible connection strings. Its more towords
turbogears way but it will get you started.
http://lucasmanual.com/mywiki/TurboGears#head-8457721059b18a5f2264f8484082deb5b294d27a
Here is sqlalchemy:
http://www.sqlalchemy.org/docs/05/dbengine.html#dbengine_establishing
(ps. could you guys add the connection strings to you docs I have
listed them in the top link. I've mentioned this before but I guess it
wasn't added to you official docs back then.)
And you should read over:
http://www.rmunn.com/sqlalchemy-tutorial/tutorial.html
How would I connect to a local MySQL database? The database I'm
working with is already designed and built, which leads me onto my
next question:
In the tutorial documentation it talks about 'Defining and Creating a
Table'. Is this something which need only be done when we're working
with an in-memory database? or do I have to define my database schema
even if its a pre-existing mysql database? or does SQLAlchemy
introspect it or something?
You auto load the existing database.
My thrid and final question is about the persistance of composed
objects. If I save an object using sql alchamy, I see it also saves
the objects children. Is this done in a transaction by default? if the
save process on one of its children fails will it all be rolled back?
or is this something I have to specify seperatly?
Thanks guys, once I know those little items I'm sure I'll be able to
get up and running pretty swiftly.
Cheers,
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting to MySQL
On Thu, Jul 10, 2008 at 11:59 AM, jason kirtland [EMAIL PROTECTED] wrote:
Lukasz Szybalski wrote:
On Thu, Jul 10, 2008 at 11:26 AM, Heston James - Cold Beans
[EMAIL PROTECTED] wrote:
Session.add is a version 0.5 method, you're maybe running 0.4.6?
In the 0.4.x series, it's going to be:
Session.save() for objects that are to be newly added to the session
Session.update() for objects that are already in the session, or
Session.save_or_update() to have the library figure it out as it does for
Session.add in v0.5.x
Hi Rick,
That's exactly what the problem was :-) Is there any reason I should avoid
using 0.5? I'm running python 2.4 at the moment, are they compatible?
Next quick question: I have a habbit of using 'created' and 'modified'
columns on my tables, is there any way in which I can have the ORM update
the dates for me when creating and modifying rows?
From the link I sent you previously:
sqlalchemy.Column('CreatedDate', sqlalchemy.Date,
default=datetime.now().date()),
sqlalchemy.Column('CreatedTime', sqlalchemy.Time,
default=datetime.now().time())
Not so much. That'll stamp every inserted row with the same time-
whatever time it was when python evaluated the Table definition.
Here's a cross-db way to get timestamps:
from sqlalchemy import Table, Column, DateTime, func
Table('abc', metadata,
...
Column('created', DateTime, default=func.now()),
Column('updated', DateTime, onupdate=func.now()))
What exactly is func ? Is that a function that just gets time or?
Can I use
onupdate=func.now().time() for time
onupdate=func.now().date() for date
I don't really prefer to have both date and time mixed in datetime field.
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy in virtualenv Instructions
On Tue, Jun 10, 2008 at 6:12 AM, King Simon-NFHD78 [EMAIL PROTECTED] wrote: Lukasz Szybalski wrote: I have mysqldb installed in the system wide install how do I tell virtualenv to use it? I don't see a need to install it in virtualenv again so I guess I just have to givea right path? How, and in which file? Thanks, Lucas File /usr/local/pythonenv/BASELINE/lib/python2.4/site-packages/SQL Alchemy-0.4.6dev_r4675-py2.4.egg/sqlalchemy/databases/mysql.py, line 1430, in dbapi import MySQLdb as mysql ImportError: No module named MySQLdb When you create the virtualenv, there is a '--no-site-packages' switch which determines whether the system-wide site-packages directory will be on the search path or not. If you didn't provide this switch, then MySQLdb should be visible. (Documented at http://pypi.python.org/pypi/virtualenv#the-no-site-packages-option) From your python prompt, type import sys; print '\n'.join(sys.path) Your command actually doesn't show the virtualenv? Is that how it suppossed to be? :~$ source /usr/local/pythonenv/BASELINE/bin/activate (BASELINE)[EMAIL PROTECTED]:~$ python Python 2.4.4 (#2, Apr 5 2007, 20:11:18) [GCC 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)] on linux2 Type help, copyright, credits or license for more information. import sys; print '\n'.join(sys.path) /usr/lib/python2.4/site-packages/Dabo-0.8.3-py2.4.egg /usr/lib/python2.4/site-packages/pyodbc-0.0.0-py2.4-linux-i686.egg /usr/lib/python2.4/site-packages/Sphinx-0.1.61950-py2.4.egg /usr/lib/python2.4/site-packages/Pygments-0.9-py2.4.egg /usr/lib/python24.zip /usr/lib/python2.4 /usr/lib/python2.4/plat-linux2 /usr/lib/python2.4/lib-tk /usr/lib/python2.4/lib-dynload /usr/local/lib/python2.4/site-packages /usr/lib/python2.4/site-packages /usr/lib/python2.4/site-packages/Numeric /usr/lib/python2.4/site-packages/cairo /var/lib/python-support/python2.4 /usr/lib/python2.4/site-packages/gtk-2.0 /var/lib/python-support/python2.4/gtk-2.0 /usr/lib/python2.4/site-packages/wx-2.6-gtk2-unicode What I did is I regenerated the virtual env without the no-site-package part, (leaving everything the way it was, just rerunning the command) which added the system packages. I hope now when I do easy_install -U somesystempackage it will install the upgrade into the virtualenv location. Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy in virtualenv Instructions
On Mon, Apr 14, 2008 at 12:12 PM, Lukasz Szybalski [EMAIL PROTECTED] wrote: On Mon, Apr 14, 2008 at 11:38 AM, jason kirtland [EMAIL PROTECTED] wrote: Lukasz Szybalski wrote: On Mon, Apr 14, 2008 at 11:30 AM, jason kirtland [EMAIL PROTECTED] wrote: Lukasz Szybalski wrote: Hello, Below you can find instructions on how to setup sqlalchemy in virtual environment. http://lucasmanual.com/mywiki/TurboGears#head-36fb4094da01b8c28e8bdca803c0f05774eb13b8 Installing SQLAlchemy in a virtualenv is the same as for any package on listed on PYPI: What does this line do? $ source myenv/bin/activate http://pypi.python.org/pypi/virtualenv#activate-script Which version of sqlalchemy does it install? current stable? trunk? or? $ easy_install SQLAlchemy The latest on pypi. You can also do $ easy_install SQLAlchemy==dev # for svn trunk $ easy_install SQLAlchemy==0.4.5 # whatever version http://peak.telecommunity.com/DevCenter/EasyInstall Thanks guys, the active script was the last piece of the puzzle. Updated: http://lucasmanual.com/mywiki/TurboGears#head-36fb4094da01b8c28e8bdca803c0f05774eb13b8 Lucas I have mysqldb installed in the system wide install how do I tell virtualenv to use it? I don't see a need to install it in virtualenv again so I guess I just have to givea right path? How, and in which file? Thanks, Lucas File /usr/local/pythonenv/BASELINE/lib/python2.4/site-packages/SQLAlchemy-0.4.6dev_r4675-py2.4.egg/sqlalchemy/databases/mysql.py, line 1430, in dbapi import MySQLdb as mysql ImportError: No module named MySQLdb --~--~-~--~~~---~--~~ 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: Transactions and read-only operations
On Sat, Jun 7, 2008 at 2:50 PM, Tomer [EMAIL PROTECTED] wrote: Thanks. What would happen if I didn't do anything (I've seen lots of examples online that will just issue a query like Session.query(User).all() and that's all). Will that query start a transaction if it's a transactional session? Currently that is what I use to do to get readonly. Since I am the one that manages sa code as long as I don't say, User.somefiled= 123, I should have no worries about any changes being made. User.query.filter.is just a select statement. Unless proven/shown otherwise. Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Creating column comments on the database
On Thu, Jun 5, 2008 at 2:37 AM, Christoph Zwerschke [EMAIL PROTECTED] wrote:
Lukasz Szybalski schrieb:
I think I prefer info dictionary rather then a string. Dict info
which I use already have something like this:
sqlalchemy.Column('DRIVE_TRAIN', sqlalchemy.Unicode(4)
,info={description:DRIVE TRAIN TYPE [AWD,4WD,FWD,RWD]}),
sqlalchemy.Column('FUEL_SYS', sqlalchemy.Unicode(4)
,info={description:FUEL SYSTEM CODE,
FI:FUEL INJECTION,
TB:TURBO}),
I think you're mixing different things here. What I suggested was
support of the database comment statement/clause supported by all major
databases (except maybe SQL Server).
I see.
What you're looking for seems to be something similar to the Domain
feature of the old Oracle Designer,
I wonder if it was possible to save the info field {} I showed above
via the comment statement/clause you are mentioning if it gets
implemented?
This would really help in describing business rules and descriptions
of each column.
Lucas
http://marceloverdijk.blogspot.com/2008/05/ref-code-plugin.html
http://its.unm.edu/ais/docs/oradomains.htm
This would be an interesting extension as well. Should be possible with
a custom TypeDecorator or TypeEngine.
-- Christoph
--
Automotive Recall Database. Cars, Trucks, etc.
http://www.lucasmanual.com/recall/
TurboGears Manual-Howto
http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf
--~--~-~--~~~---~--~~
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] mssql and offset support
Hello, From Turbogears website I found out that mssql doesn't support offset query. read below. On Wed, Jun 4, 2008 at 4:20 PM, Lukasz Szybalski [EMAIL PROTECTED] wrote: Paginate uses the same output from the same method, but slices the result differently, that's why it is important to not use .all() (if you are not on MSSQL server, because MSSQL server does not support the use of offsets in queries). I took off the .all() just to see what happens and here comes the error. So I guess I have to use .all()?!? File /usr/local/pythonenv/BASELINE/lib/python2.4/site-packages/SQLAlchemy-0.4.6dev_r4675-py2.4.egg/sqlalchemy/databases/mssql.py, line 498, in do_execute cursor.execute(SET IDENTITY_INSERT %s OFF % self.identifier_preparer.format_table(context.compiled.statement.table)) SystemError: 'finally' pops bad exception Error location in template file '/home/unique/turbogears///templates/active.kid' on line 25 between columns 3 and 72: ... span py:content=activegrid(active)some content/span ... File /usr/local/pythonenv/BASELINE/lib/python2.4/site-packages/SQLAlchemy-0.4.6dev_r4675-py2.4.egg/sqlalchemy/databases/mssql.py, line 498, in do_execute What is interesting is the end of the traceback not the beginning, but I suspect the error you get is because you are using MSSQL and it does not support offsets. I know some people are working on implementing an offest support for MSSQL to be able to use proxy results instead of using all() on MSSQL. I guess my question is, if wanted to use offset. How would I do it? Postgresql, Mysql Oracle all support offsets. AFAIK Oracle doesn't. You can fake it using an embedded SQL and the implicit rownum-column. but that will cost performance. If you can, keep a referernce to the cursor around. Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Creating column comments on the database
On Wed, Jun 4, 2008 at 7:41 AM, Christoph Zwerschke [EMAIL PROTECTED] wrote: Does SQLAlchemy support comment on column or comments on other database objects? I expected Column to have a comment keyword, but found nothing of that kind. Maybe I am missing something? I find column comments often useful, and though they may not be SQL standard, they are supported by all major databases. I started using info dict on a column names in sa file definitions. It would be nice to save it to db if once could. What db supports comments and what table name is it? Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql and offset support
On Wed, Jun 4, 2008 at 10:22 AM, Rick Morrison [EMAIL PROTECTED] wrote: There's offset support in the current sqla mssql driver. It's implemented using the ansi ROW_NUMBER() OVER construct, which is supported only in mssql 2005 and higher. Since I am using 2000 I don't think its going to work for me do?!? To turn it on, add the has_window_funcs keyword in the dburi, or as an engine constructor keyword. the broken traceback is some as-of-yet unknown issue with pyodbc that reports the wrong stack trace. -- Automotive Recall Database. Cars, Trucks, etc. http://www.lucasmanual.com/recall/ TurboGears Manual-Howto http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf --~--~-~--~~~---~--~~ 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: Creating column comments on the database
On Wed, Jun 4, 2008 at 12:09 PM, Christoph Zwerschke [EMAIL PROTECTED] wrote:
Lukasz Szybalski schrieb:
I started using info dict on a column names in sa file definitions. It
would be nice to save it to db if once could. What db supports
comments and what table name is it?
Oracle and PostgreSQL have the comment on SQL statement. MySQL has a
comment clause for column definitions. SA could abstract away these
differences, the syntax would be something like this:
users = Table('users', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(40), comment=The Unix user name),
Column('fullname', String(100), comment=Full name with title))
I think I prefer info dictionary rather then a string. Dict info
which I use already have something like this:
sqlalchemy.Column('DRIVE_TRAIN', sqlalchemy.Unicode(4)
,info={description:DRIVE TRAIN TYPE [AWD,4WD,FWD,RWD]}),
sqlalchemy.Column('FUEL_SYS', sqlalchemy.Unicode(4)
,info={description:FUEL SYSTEM CODE,
FI:FUEL INJECTION,
TB:TURBO}),
Would be nice if the whole dictionary was written to a database. I
could use info[description], info[FI], info[TB] etc... when I display
data. That would be really useful definition!!!
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] how many query objects in sa?
Hello, I have used the following to query my data: #start class th(object): pass mapper(th,th_table) a=session.query(th).filter(sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).all() #end I noticed that there is a query th.query().filter(sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).all() but it gives me a : AttributeError: 'generator' object has no attribute 'all' What is the difference between these queries? Both of them are ORM based since they used mapped python class?!? Which one should be used? And why I wouldn't use one of them? Lucas -- Automotive Recall Database. Cars, Trucks, etc. http://www.lucasmanual.com/recall/ TurboGears Manual-Howto http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf --~--~-~--~~~---~--~~ 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 many query objects in sa?
On Wed, May 28, 2008 at 11:59 AM, Michael Bayer [EMAIL PROTECTED] wrote: On May 28, 2008, at 12:36 PM, Lukasz Szybalski wrote: Hello, I have used the following to query my data: #start class th(object): pass mapper(th,th_table) a = session .query (th ).filter (sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).all() #end I noticed that there is a query th .query ().filter (sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).all() but it gives me a : AttributeError: 'generator' object has no attribute 'all' something else is going on there. filter() on Query returns a new Query in all cases. What is th.query() ? Since the session is bound to the mapped classes(in tg and in sa). I shouldn't have to use session.query(mymappedclass).somfilter What I want to do is convert the: session.query(th).filter(sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).all() to just a query using mapped class. Asumed that session.query(th).somefilter is same as th.query().somefilter would do it, but obviously these two are different. So I my question is, are the session.query(th).somefilter vs th.query().somefilter different or am I missing something here. Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how many query objects in sa?
On Wed, May 28, 2008 at 1:07 PM, Lukasz Szybalski [EMAIL PROTECTED] wrote: On Wed, May 28, 2008 at 11:59 AM, Michael Bayer [EMAIL PROTECTED] wrote: On May 28, 2008, at 12:36 PM, Lukasz Szybalski wrote: Hello, I have used the following to query my data: #start class th(object): pass mapper(th,th_table) a = session .query (th ).filter (sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).all() #end I noticed that there is a query th .query ().filter (sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).all() but it gives me a : AttributeError: 'generator' object has no attribute 'all' something else is going on there. filter() on Query returns a new Query in all cases. What is th.query() ? Since the session is bound to the mapped classes(in tg and in sa). I shouldn't have to use session.query(mymappedclass).somfilter What I want to do is convert the: session.query(th).filter(sqlalchemy.and_(th.APPLIED_TEST==1,th.CODING_DATE=='20080325')).all() to just a query using mapped class. sorry. I copied the wrong query Just for clarification: session.query(th).somefilter is same as th.query().somefilter correct? Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how many query objects in sa?
On Wed, May 28, 2008 at 2:07 PM, Michael Bayer [EMAIL PROTECTED] wrote: On May 28, 2008, at 2:44 PM, Lukasz Szybalski wrote: sorry. I copied the wrong query Just for clarification: session.query(th).somefilter is same as th.query().somefilter correct? there is a query attribute added to mapped classes if you use the mapper function provided by ScopedSession. Alternatively, ScopedSession also has a method called query_property which can be used to add a similar attribute without the extra functionality implied by ScopedSession.mapper.Otherwise, theres no such attribute query added to mapped classes, and you haven't specified if you're using one of these extensions. The behavior of the query attribute provided by these libraries is to just return a Query so there's no difference in behavior. It is usually common to access the attribute as a descriptor, i.e. cls.query.filter(..) This implementation is in Turbogears so turbogears handles the session management. I assume they have to be using the ScopedSession. Thanks a lot. Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: filtering by datetime elements
On Sat, May 17, 2008 at 11:20 AM, Eric Abrahamsen [EMAIL PROTECTED] wrote: On May 17, 2008, at 11:17 PM, [EMAIL PROTECTED] wrote: it's SQL that is not working with python objects, and the column pubdate (associated with type DateTime on python side) has no attr .year or .month. lookup the messages in the group, there were some sugestions long time ago, but AFAIremember one was something with strings, another with separate columns. Thanks svil, this is good to know. I suppose there's no reason why I can't pull a simpler query into Python and then filter it by date there. It seems like doing this in the SQL query is going to be hackish no matter what, particularly when it's so simple to do in Python... you could convert your month variable to same format as article.pubdate (datetime) http://lucasmanual.com/mywiki/PythonManual#head-7b8d3475aa2baaa193b02b72fccd6eb009a1ee63 or modify the datetime to separate date and time columns. Lucas -- Automotive Recall Database. Cars, Trucks, etc. http://www.lucasmanual.com/recall/ TurboGears Manual-Howto http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf --~--~-~--~~~---~--~~ 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: Access to AS/400 data
On Fri, May 16, 2008 at 12:03 PM, Carlos Hanson [EMAIL PROTECTED] wrote: On Fri, May 16, 2008 at 8:14 AM, Michael Bayer [EMAIL PROTECTED] wrote: On May 16, 2008, at 10:55 AM, Carlos Hanson wrote: On Fri, May 16, 2008 at 6:13 AM, Jim Steil [EMAIL PROTECTED] wrote: Hi: Can anyone tell me if it is possible to access data on an AS/400 through SQLAlchemy? -Jim I'm connecting to an AS/400 using pyodbc, so I am sure that I can do it through SQLAlchemy. If I have a chances to test it, I'll post my success. But if you get an ODBC connection set up, the re should be no problem. well, connecting is just the beginning. to take advantage of SQLA, you would also want an AS/400 dialect that knows how to render SQL in the way an AS/400 likes. Im not familiar with anyone working on an AS/ 400 dialect at the moment. I only know of the DB2 dialect which is a separate project (but maybe ask on their list since they work for IBM). This is a good point. I have to create aliases to a file/member combination to select data. I guess I wouldn't expect SQLAlchemy to implement that by default, since most every other database uses tables. What is your connection string in pyodbc for as400? When you create aliases then select statements work as they should for you? Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Connecting to an MS SQL server ?
On Thu, May 15, 2008 at 12:51 PM, Yannick Gingras [EMAIL PROTECTED] wrote:
TkNeo [EMAIL PROTECTED] writes:
Hi,
Hello Tarun,
This is my first encounter with sqlalchemy. I am trying to connect to
an MS SQL server 2000 that is not on local host. I want to connect
using Integrated Security and not use a specific username and
password. Can anyone tell me the format of the connection string ?
I don't know about Integrated Security but we use alchemy to connect
to a MSSQL from a GNU/Linux box and it works really well. We use Unix
ODBC with TDS with the DSN registered with the local ODBC.
Take a look at
http://www.lucasmanual.com/mywiki/TurboGears#head-4a47fe38beac67d9d03e49c4975cfc3dd165fa31
My obdb.ini looks like
[JDED]
Driver = TDS
Trace = No
Server = 192.168.33.53
Port= 1433
and my alchemy connection string is
mssql://user:pass@/?dsn=JDEDscope_identity=1
Not sure what platform you are using but on linux I use:
e = sqlalchemy.create_engine(mssql://user:[EMAIL
PROTECTED]:1433/database?driver=TDSodbc_options='TDS_Version=8.0')
but you need sa 0.4.6.
on windows you can use:
e = sqlalchemy.create_engine('mssql://user:[EMAIL PROTECTED]:1433/database')
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: database definitions - was sqlalchemy migration/schema creation
On Fri, May 9, 2008 at 10:14 AM, [EMAIL PROTECTED] wrote:
On Friday 09 May 2008 16:32:25 Lukasz Szybalski wrote:
On Fri, May 9, 2008 at 4:46 AM, [EMAIL PROTECTED] wrote:
On Friday 09 May 2008 03:05, Lukasz Szybalski wrote:
Do you guys know what would give me column definition of table?
do u want it as generated source-text or what?
Yes. The Final output I would like is the txt version of db
definitions.
autoload --- sqlalchemy.Column('Address_Sid', sqlalchemy.Integer,
primary_key=True),
well, then see that one.
have a look at dbcook/dbcook/misc/metadata/autoload.py
at dbcook.sf.net
running 'python autoload.py dburl' will dump the db-metadata into
src-text,
I keep getting this error:
python autoload.py mysql://user:[EMAIL PROTECTED]/mytable
Traceback (most recent call last):
File autoload.py, line 204, in ?
assert 0, 'unsupported engine.dialect:'+str( engine.dialect)
AssertionError: unsupported
engine.dialect:sqlalchemy.databases.mysql.MySQLDialect object at
0x2af7f6f522d0
(ENV)[EMAIL PROTECTED]:~/tmp/metadata$ python autoload.py
mysql://root:[EMAIL PROTECTED]/mysql
mysql://root:[EMAIL PROTECTED]/mysql
Traceback (most recent call last):
File autoload.py, line 204, in ?
assert 0, 'unsupported engine.dialect:'+str( engine.dialect)
AssertionError: unsupported
engine.dialect:sqlalchemy.databases.mysql.MySQLDialect object at
0x2b11f2dcb2d0
Any ideas?
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: database definitions - was sqlalchemy migration/schema creation
On Fri, May 9, 2008 at 4:46 AM, [EMAIL PROTECTED] wrote:
On Friday 09 May 2008 03:05, Lukasz Szybalski wrote:
Do you guys know what would give me column definition of table?
do u want it as generated source-text or what?
Yes. The Final output I would like is the txt version of db definitions.
autoload --- sqlalchemy.Column('Address_Sid', sqlalchemy.Integer,
primary_key=True),
have a look at dbcook/dbcook/misc/metadata/autoload.py
at dbcook.sf.net
I have a table that I autoload and I would like to get this:
address_table = sqlalchemy.Table('address', metadata,
sqlalchemy.Column('Address_Sid', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('FirstName', sqlalchemy.Unicode(40),nullable=False),
sqlalchemy.Column('LastName', sqlalchemy.Unicode(40),nullable=False),
sqlalchemy.Column('MaidenLastName', sqlalchemy.Unicode(40)),
sqlalchemy.Column('Email', sqlalchemy.Unicode(80),nullable=False),
So:
I can get a list of tables:
for i in metadata.tables:
... print i
list of columns:
for i in metadata.tables['WMI'].original_columns:
... print i
How do I get column type, indexes and primary keys?
Lucas
On Tue, Apr 15, 2008 at 7:44 PM, Lukasz Szybalski [EMAIL PROTECTED]
wrote:
Hello,
Is there maybe a feature in sqlalchemy that would allow me to autoload
table from one database, and move it over to another database?
1. I would like to print data structure from autoload table. (then
copy and paste it into new file and use it to create new
table)(without typing every data structure)
2. And/or autoload via one engine and autoupload via different engine
and create_all()
3. Analyze csv files and create sqlalchemy definition like structure.
Ideas?
Lucas
--
Automotive Recall Database. Cars, Trucks, etc.
http://www.lucasmanual.com/recall/
TurboGears Manual-Howto
http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf
--~--~-~--~~~---~--~~
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] database definitions - was sqlalchemy migration/schema creation
Do you guys know what would give me column definition of table?
I have a table that I autoload and I would like to get this:
address_table = sqlalchemy.Table('address', metadata,
sqlalchemy.Column('Address_Sid', sqlalchemy.Integer, primary_key=True),
sqlalchemy.Column('FirstName', sqlalchemy.Unicode(40),nullable=False),
sqlalchemy.Column('LastName', sqlalchemy.Unicode(40),nullable=False),
sqlalchemy.Column('MaidenLastName', sqlalchemy.Unicode(40)),
sqlalchemy.Column('Email', sqlalchemy.Unicode(80),nullable=False),
So:
I can get a list of tables:
for i in metadata.tables:
... print i
list of columns:
for i in metadata.tables['WMI'].original_columns:
... print i
How do I get column type, indexes and primary keys?
Lucas
On Tue, Apr 15, 2008 at 7:44 PM, Lukasz Szybalski [EMAIL PROTECTED] wrote:
Hello,
Is there maybe a feature in sqlalchemy that would allow me to autoload
table from one database, and move it over to another database?
1. I would like to print data structure from autoload table. (then
copy and paste it into new file and use it to create new
table)(without typing every data structure)
2. And/or autoload via one engine and autoupload via different engine
and create_all()
3. Analyze csv files and create sqlalchemy definition like structure.
Ideas?
Lucas
--
Automotive Recall Database. Cars, Trucks, etc.
http://www.lucasmanual.com/recall/
TurboGears Manual-Howto
http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf
--~--~-~--~~~---~--~~
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: mssql, Linux, unixODBC - Could not locate column in row for column
On Tue, Apr 22, 2008 at 9:35 AM, Lukasz Szybalski [EMAIL PROTECTED] wrote:
e = sqlalchemy.create_engine(mssql://xxx:[EMAIL
PROTECTED]:1433/xx?driver=TDSodbc_options='TDS_Version=8.0')
here is a patch to mssql.py that makes above line work.
805c805,808
connectors.append(keys.pop('odbc_options'))
---
odbc_options=keys.pop('odbc_options')
if odbc_options[0]==' and odbc_options[-1]==':
odbc_options=odbc_options[1:-1]
connectors.append(odbc_options)
Could you guys add it in to svn.
Any news on this? Should I create a ticket so we don't forget about this?
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: multiple databases ?
On Tue, Apr 22, 2008 at 11:54 AM, Lukasz Szybalski [EMAIL PROTECTED] wrote:
Hello again,
So now that I have mssql connection ready and data filtered out and
processed I need to save it to a different database.
mssql - process data - save to mysql
I am wondering how should I create a second database connection? In
second database I will create a table and populate the records.
# First database
e = sqlalchemy.create_engine(mssql://user:[EMAIL
PROTECTED]:1433/dbname?driver=TDSodbc_options='TDS_Version=8.0')
#e.echo=True
metadata=sqlalchemy.MetaData(e)
#session stuff
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, transactional=True)
session = Session()
#table stuff
class th(object):
pass
th_table = sqlalchemy.Table('', metadata, autoload=True)
mapper(th,th_table)
# database number 2. Is this the way I should create second database
connection/session/mapper?
e2 = sqlalchemy.create_engine('mysql://user:[EMAIL PROTECTED]/dbname')
Do I create new metadata?
metadata2=sqlalchemy.MetaData(e2)
And then new session2?
Session2 = sessionmaker(bind=e2, autoflush=True, transactional=True)
What does this line do?
session = Session()
Is the above correct way of doing this?
I would like to keep these connections separate so there will be no
confusion of what I am using.
Thanks,
Lucas
Just an FYI.
Setting up 2 meta data, 2 sessions worked out pretty good.
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
e = sqlalchemy.create_engine(mssql://xxx:[EMAIL
PROTECTED]:1433/xx?driver=TDSodbc_options='TDS_Version=8.0')
here is a patch to mssql.py that makes above line work.
805c805,808
connectors.append(keys.pop('odbc_options'))
---
odbc_options=keys.pop('odbc_options')
if odbc_options[0]==' and odbc_options[-1]==':
odbc_options=odbc_options[1:-1]
connectors.append(odbc_options)
Could you guys add it in to svn.
Thanks,
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] multiple databases ?
Hello again,
So now that I have mssql connection ready and data filtered out and
processed I need to save it to a different database.
mssql - process data - save to mysql
I am wondering how should I create a second database connection? In
second database I will create a table and populate the records.
# First database
e = sqlalchemy.create_engine(mssql://user:[EMAIL
PROTECTED]:1433/dbname?driver=TDSodbc_options='TDS_Version=8.0')
#e.echo=True
metadata=sqlalchemy.MetaData(e)
#session stuff
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, transactional=True)
session = Session()
#table stuff
class th(object):
pass
th_table = sqlalchemy.Table('', metadata, autoload=True)
mapper(th,th_table)
# database number 2. Is this the way I should create second database
connection/session/mapper?
e2 = sqlalchemy.create_engine('mysql://user:[EMAIL PROTECTED]/dbname')
Do I create new metadata?
metadata2=sqlalchemy.MetaData(e2)
And then new session2?
Session2 = sessionmaker(bind=e2, autoflush=True, transactional=True)
What does this line do?
session = Session()
Is the above correct way of doing this?
I would like to keep these connections separate so there will be no
confusion of what I am using.
Thanks,
Lucas
--
Automotive Recall Database. Cars, Trucks, etc.
http://www.lucasmanual.com/recall/
TurboGears Manual-Howto
http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf
--~--~-~--~~~---~--~~
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: multiple databases ?
On Tue, Apr 22, 2008 at 6:00 PM, [EMAIL PROTECTED] wrote:
On Tuesday 22 April 2008 19:54:16 Lukasz Szybalski wrote:
Hello again,
So now that I have mssql connection ready and data filtered out and
processed I need to save it to a different database.
mssql - process data - save to mysql
I am wondering how should I create a second database connection? In
second database I will create a table and populate the records.
# First database
e =
sqlalchemy.create_engine(mssql://user:[EMAIL PROTECTED]:1433/dbname?dr
iver=TDSodbc_options='TDS_Version=8.0') #e.echo=True
metadata=sqlalchemy.MetaData(e)
#session stuff
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=e, autoflush=True, transactional=True)
session = Session()
#table stuff
class th(object):
pass
th_table = sqlalchemy.Table('', metadata, autoload=True)
mapper(th,th_table)
# database number 2. Is this the way I should create second
database connection/session/mapper?
e2 = sqlalchemy.create_engine('mysql://user:[EMAIL PROTECTED]/dbname')
Do I create new metadata?
metadata2=sqlalchemy.MetaData(e2)
And then new session2?
Session2 = sessionmaker(bind=e2, autoflush=True,
transactional=True) What does this line do?
session = Session()
Is the above correct way of doing this?
I would like to keep these connections separate so there will be no
confusion of what I am using.
Thanks,
Lucas
well... u dont really need 2nd metadata, and u dont need any
session/mappers/etc ORM-stuff at all. re-bind the metadata to new
engine, then metadata.createall(), and then for each record in each
table in metadata, tbl.select via src_engine, tbl.insert via
dst_engine
but u can try this way too..
these are 2 different databases...
one is all autoload
and two is completely new and will not have any tables from 1.
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Fri, Apr 18, 2008 at 11:07 AM, jason kirtland [EMAIL PROTECTED] wrote:
Rick Morrison wrote:
Yeah, I was under the impression that config args passed in via
create_engine() ctor and via dburi were treated the same, but looking
over engine/strategies.py, it looks as if they have two separate
injection points. I'll see if I can get it to allow either, stay tuned.
create_engine('mssql://h/db', connect_args=dict(odbc_options='bar'))
create_engine('mssql://h/db?odbc_options=bar')
so to make it clear.
create_engine('mssql://h/db',
connect_args=dict(odbc_options='DRIVER={TDS};TDS_Version=8.0'))
This doesn't append driver and tds version to connection string. Isn't
connect_args for sqlalchemy specific actions and not for pyodbc
connector string.
create_engine('mssql://h/db?odbc_options=bar')
this will work if I had a single option, but I will supply multiple
options with '=' operator.
Example: This string need to be appended to connection string when
passed to pyodbc.
'DRIVER={TDS};TDS_Version=8.0'
so If you guys don't mind making the whole expression double quotes we
can move on to:
create_engine(mssql://h/db?odbc_options='DRIVER={TDS};TDS_Version=8.0' )
but now the driver that I just supplied in (line 804), was previously
added in 783 so now you have 2 drivers.
which leads me to try:
e = sqlalchemy.create_engine(mssql://xxx:[EMAIL
PROTECTED]:1433/xxx?driver=TDS?odbc_options='TDS_Version=8.0')
this only takes the first argument driver and skips odbc_options
solution :
option 1:
make it so mssql://xxx:[EMAIL
PROTECTED]:1433/xxx?driver=TDS?odbc_options='TDS_Version=8.0'
handles both parameters driver and odbc_options
option 2:
We either move line 804 above line 783 and then check if it includes
driver already?
line 784
if 'dsn' in keys:
connectors = ['dsn=%s' % keys['dsn']]
else:
connectors = [DRIVER={%s} % keys.pop('driver', 'SQL Server'),
'Server=%s' % keys['host'],
'Database=%s' % keys['database'] ]
line 804:
if 'odbc_options' in keys:
connectors.append(keys.pop('odbc_options'))
or option3?
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Fri, Apr 18, 2008 at 1:36 PM, Rick Morrison [EMAIL PROTECTED] wrote:
Err, on a second look, that's no good. The connect_args are passed directly
through to connect().
This thing needs to construct an ODBC connection string from some fragments
provided by the dburi, and from some engine options. So we'll either need
some parsing tricks for the URL to allow strings with embedded equals signs,
or some way to get that parameter to Dialect.create_connect_args() so it can
be combined with the username, etc.
I've looked up how to pass these arguments and this will work:
e = sqlalchemy.create_engine(mssql://xxx:[EMAIL
PROTECTED]:1433/xx?driver=TDSodbc_options='TDS_Version=8.0')
I'll test this and see if it works.
Lucas
create_engine('mssql://h/db', connect_args=dict(odbc_options='bar'))
create_engine('mssql://h/db?odbc_options=bar')
--
Automotive Recall Database. Cars, Trucks, etc.
http://www.lucasmanual.com/recall/
TurboGears Manual-Howto
http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf
--~--~-~--~~~---~--~~
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: mssql, Linux, unixODBC - Could not locate column in row for column
On Mon, Apr 7, 2008 at 4:37 PM, Rick Morrison [EMAIL PROTECTED] wrote:
The limitation here I guess is 30 char identifier limit but I will
need to test it.
Ah yeah, you're going to have bigger problems than 30 char identifiers with
the Sybase TDS settings. MSSQL uses a different set of wire-protocol
representations of datetime types, and your dates are going to be off.
In order to use more characters the FreeTDS should be configured to
use TDS protocol 7.0 which:
Even that one is old: MSSQL switched to TDSv7 back in MSSQL 7.0 (circa 1997)
They're now on TDSv8 as of MSSQL-2000
Here's a chart:
http://www.freetds.org/tds.html
It beats me why FreeTDS still defaults to Sybase instead of MSSQL, the
installed base has to run at least 100 to 1 in favor of MSSQL. Oh well.
When you get all this set up correctly, you may want to update the
sqlalchemy wiki with all this stuff.
What I have found out is that in dsn less connection is not going
through the freetds.conf.
So me settings things there has no point.
So the way we passed DRIVER={TDS} I would also have to pass TDS_Version
cnxn = pyodbc.connect(SERVER=xxx;UID=xxx;PWD=xxx;DRIVER={TDS};TDS_Version=7.0)
this actually returns results that are longer then 30.
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Thu, Apr 17, 2008 at 10:02 AM, Lukasz Szybalski [EMAIL PROTECTED] wrote:
On Mon, Apr 7, 2008 at 4:37 PM, Rick Morrison [EMAIL PROTECTED] wrote:
The limitation here I guess is 30 char identifier limit but I will
need to test it.
Ah yeah, you're going to have bigger problems than 30 char identifiers with
the Sybase TDS settings. MSSQL uses a different set of wire-protocol
representations of datetime types, and your dates are going to be off.
In order to use more characters the FreeTDS should be configured to
use TDS protocol 7.0 which:
Even that one is old: MSSQL switched to TDSv7 back in MSSQL 7.0 (circa
1997)
They're now on TDSv8 as of MSSQL-2000
Here's a chart:
http://www.freetds.org/tds.html
It beats me why FreeTDS still defaults to Sybase instead of MSSQL, the
installed base has to run at least 100 to 1 in favor of MSSQL. Oh well.
When you get all this set up correctly, you may want to update the
sqlalchemy wiki with all this stuff.
What I have found out is that in dsn less connection is not going
through the freetds.conf.
So me settings things there has no point.
So the way we passed DRIVER={TDS} I would also have to pass TDS_Version
cnxn =
pyodbc.connect(SERVER=xxx;UID=xxx;PWD=xxx;DRIVER={TDS};TDS_Version=7.0)
this actually returns results that are longer then 30.
Lucas
On Thu, Apr 17, 2008 at 11:09 AM, Rick Morrison [EMAIL PROTECTED] wrote:
Two points:
- Unless you're running Sybase, it's possible to set the default TDS version
in the TDS config file, which would eliminate the need for this.
- The end point of all of these parms is to simply build an ODBC connection
string. Wouldn't a parm that just allowed the specification of the string
as-is be just as useful but a lot more flexible/simpler?
Here are the options as specified by free TDS. What you are talking
about is setting it in conf file which is used only for dsn
connection.
pyodbc takes this TDS_Version parameter with no problems.
here is what freetds said:
to use TDS 7.0 in a DSN-less connection, your options are:
1. Rebuild FreeTDS --with-tdsver=7.0, or
2. Set the environment variable TDSVER=7.0 before starting Python, or
3. Add TDS_Version=7.0; to your connection string.
4. Use Servername in your connection string.
options 3 seems the easies to me.
Let me know
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Thu, Apr 17, 2008 at 12:14 PM, Rick Morrison [EMAIL PROTECTED] wrote:
Here are the options as specified by free TDS. What you are talking
about is setting it in conf file which is used only for dsn
connection.
No, I meant as the *default* TDS version here. See here:
http://www.freetds.org/userguide/freetdsconf.htm
The way I read the comment from freetds
http://lists.ibiblio.org/pipermail/freetds/2008q2/023126.html
connection string:
cnxn = pyodbc.connect(SERVER=xxx;UID=xxx;PWD=xxx;DRIVER={TDS};TDS_Version=7.0)
Connecting this way does not read the freetds.conf, it goes directly
to the driver.
I'm talking about the [global] setting, which is the default used unless
overridden in a different [dataserver] config.
I have changed it in the global settings and it made no difference. It
was only when I emailed them I have found out that the way we connect
is not using freetds.conf. So in order to get the higher version I
would have to recompile freetds.
here is what freetds said:
to use TDS 7.0 in a DSN-less connection, your options are:
1. Rebuild FreeTDS --with-tdsver=7.0, or
2. Set the environment variable TDSVER=7.0 before starting Python, or
3. Add TDS_Version=7.0; to your connection string.
4. Use Servername in your connection string.
or 5. Change the default version to the desired version
Not if you use direct connection. It would if we used ?dsn=
OK. I'm just suggesting that if you'd rather specify the version in the
dburi instead of changing it on the server, that we allow the specification
of the ODBC connect string directly, rather than provide a bunch of separate
parameters that are in turn only used to build an ODBC connection string
anyway.
I don't see an easy option to change it. If it was as easy as changing
freetds.conf then I would do it, but its not.
Finally, as I mentioned in an earlier thread, you should most likely be
using TDS version 8.0, not 7.0.
Thanks,
I'll use 8.0
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Thu, Apr 17, 2008 at 2:07 PM, Rick Morrison [EMAIL PROTECTED] wrote:
It's in trunk r4518. Take 'er for a spin and let me know how it works out.
got an error:
e = sqlalchemy.create_engine('mssql://xxx:[EMAIL
PROTECTED]:1433/',odbc_options='DRIVER={TDS};TDS_Version=8.0')
Traceback (most recent call last):
File stdin, line 1, in ?
File sqlalchemy/engine/__init__.py, line 160, in create_engine
return strategy.create(*args, **kwargs)
File sqlalchemy/engine/strategies.py, line 114, in create
raise TypeError(
TypeError: Invalid argument(s) 'odbc_options' sent to create_engine(),
using configuration MSSQLDialect_pyodbc/QueuePool/Engine. Please
check that the keyword arguments are appropriate for this combination
of components.
Does it matter what case are the parameters? DRIVER in pyodbc, we used
'driver' in previous connection strings etc...
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Thu, Apr 17, 2008 at 2:35 PM, Rick Morrison [EMAIL PROTECTED] wrote:
Does it matter what case are the parameters? DRIVER in pyodbc, we used
'driver' in previous connection strings etc...
No the parameters are a straight pass-through, that traceback is complaining
about the 'odbc_options' keyword itself. Are you sure you're running the
current trunk?
svn update
At revision 4518.
[EMAIL PROTECTED]:~/tmp/sqlalchemy/sqlalchemy/lib$ python
Python 2.4.4 (#2, Apr 5 2007, 20:11:18)
[GCC 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)] on linux2
Type help, copyright, credits or license for more information.
import sqlalchemy
sqlalchemy.__version__
'svn'
e = sqlalchemy.create_engine('mssql://xxx:[EMAIL
PROTECTED]:1433/xxx',odbc_options='DRIVER={TDS};TDS_Version=8.0')
Traceback (most recent call last):
File stdin, line 1, in ?
File sqlalchemy/engine/__init__.py, line 160, in create_engine
return strategy.create(*args, **kwargs)
File sqlalchemy/engine/strategies.py, line 114, in create
raise TypeError(
TypeError: Invalid argument(s) 'odbc_options' sent to create_engine(),
using configuration MSSQLDialect_pyodbc/QueuePool/Engine. Please
check that the keyword arguments are appropriate for this combination
of components.
not sure why strategies.py would complain?
Are you converting:
'mssql://xxx:[EMAIL
PROTECTED]:1433/xxx',odbc_options='DRIVER={TDS};TDS_Version=8.0'
to
SERVER=xxx;UID=xx;PWD=xxx;DRIVER={TDS};TDS_Version=7.0
I have added the print line that we had used before that outputs the
connection string:
import sqlalchemy
e = sqlalchemy.create_engine('mssql://xx:[EMAIL
PROTECTED]:1433/xxx',odbc_options='Driver=TDS;TDS_Version=8.0')
DRIVER={SQL Server};Server=xxx;Database=xxx;Port=1433;UID=xxx;PWD=xxx
The driver is not changed and tds_version is not added
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: mssql, Linux, unixODBC - Could not locate column in row for column
On Thu, Apr 17, 2008 at 3:04 PM, Rick Morrison [EMAIL PROTECTED] wrote:
It's a two-line change that pops the new keyword out of the config dict just
like the others that were added.
Mike, can you take a quick look at mssql.py line 804 and see why this might
be complaining? I've got to run out.
well I don't know if that is a right place to add that?
from the code this what it would expect
e = sqlalchemy.create_engine(mssql://xxx:[EMAIL
PROTECTED]:1433/xxx?odbc_options=Driver=TDS;TDS_Version=8.0)
vs you said you wanted:
sqlalchemy.create_engine('mssql://xxx:[EMAIL
PROTECTED]:1433/',odbc_options='DRIVER={TDS};TDS_Version=8.0')
to create
Server=xx;Database=xx;Port=1433;UID=xx;PWD=xxx;Driver={TDS};TDS_Version=8.0
Lucas
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: sqlalchemy migration/schema creation
On Wed, Apr 16, 2008 at 1:03 AM, [EMAIL PROTECTED] wrote: a separate feature - no. but it's not much to do it. here my attempt at this. try copyall or copydata+autoload from here: http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/metadata/ svn co http://dbcook.svn.sourceforge.net/dbcook/trunk/dbcook/misc/metadata/ this works: svn co https://dbcook.svn.sourceforge.net/svnroot/dbcook/trunk/dbcook/misc/metadata/ At which point here is the table created in dst database? metadata = autoloader.metadata() here you get the src metadata about tables?!! dst_engine= sqlalchemy.create_engine( dbdst) Here you change the engine?? metadata.bind = dst_engine This will create tables from the first line statement? metadata.create_all() Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] sqlalchemy migration/schema creation
Hello, Is there maybe a feature in sqlalchemy that would allow me to autoload table from one database, and move it over to another database? 1. I would like to print data structure from autoload table. (then copy and paste it into new file and use it to create new table)(without typing every data structure) 2. And/or autoload via one engine and autoupload via different engine and create_all() 3. Analyze csv files and create sqlalchemy definition like structure. Ideas? Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] sqlalchemy in virtualenv Instructions
Hello, Below you can find instructions on how to setup sqlalchemy in virtual environment. http://lucasmanual.com/mywiki/TurboGears#head-36fb4094da01b8c28e8bdca803c0f05774eb13b8 Enjoy, Lucas -- Automotive Recall Database. Cars, Trucks, etc. http://www.lucasmanual.com/recall/ Install Broadcom wireless card on Linux: http://lucasmanual.com/mywiki/bcm43xx --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
