date:20180110

Re: [sqlalchemy] dialect issue with pyodbc.Row type

2018-01-10 Thread Russ Wilson

Thanks for the insights

On Tue, Jan 9, 2018 at 10:23 PM Mike Bayer  wrote:

> On Tue, Jan 9, 2018 at 8:45 PM, Russ Wilson  wrote:
> > So i loaded and tested the mmsql dialect and it gave the same results. It
> > returns a list of pyodbc.Row
> >
> > from sqlalchemy import Column, Integer, String
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy import create_engine
> > from sqlalchemy import Table, Column, Integer, String, MetaData,
> ForeignKey
> > from sqlalchemy import inspect
> > from sqlalchemy.dialects import registry
> >
> >
> > engine =
> > create_engine("mssql+pyodbc://MYUSER:MYPASSWORD@IP
> :1433/corn?driver=FreeTDS")
> > connection = engine.raw_connection()
> > try:
> > cursor = connection.cursor()
> > cursor.execute("SELECT *  FROM ADV.MYTABLE")
> > results_one = cursor.fetchmany(100)
> > for row in results_one:
> > print(type(row))
> >
> > cursor.close()
> > finally:
> > connection.close()
>
> When you use raw_connection(), you are stating that you would like to
> use a raw DBAPI connection object, and you are no longer using the
> SQLAlchemy dialect.   You are using the plain pyodbc cursor directly,
> which is usually not necessary unless you need to work with stored
> procedures or special cursor methods.
>
> Using SQLAlchemy normally, your code above would be:
>
> with engine.connect() as conn:
> result = conn.execute("SELECT * FROM ADV.MYTABLE")
>
> result is then a ResultProxy object and returns RowProxy objects when
> you call fetchone(), fetchmany(), and fetchall().RowProxy then
> acts like a tuple.
>
>
>
>
> >
> > On Tuesday, January 9, 2018 at 4:38:54 PM UTC-6, Mike Bayer wrote:
> >>
> >> There's the README at
> >> https://github.com/zzzeek/sqlalchemy/blob/master/README.dialects.rst
> which
> >> also includes some links to an example dialect.
> >>
> >> On Jan 9, 2018 12:35 PM, "Russ Wilson"  wrote:
> >>
> >> Is there a good doc that covered at at min needs to be extended to
> create
> >> a dialect?
> >>
> >> On Mon, Jan 8, 2018 at 3:15 PM Mike Bayer 
> wrote:
> >>>
> >>> On Sun, Jan 7, 2018 at 9:07 PM, Russ Wilson  wrote:
> >>> > I noticed if you use the cursor.fetchmany it returns the pyodbc
> types.
> >>> > Is
> >>> > this an issue with the dialect?  if you use the connection execute
> you
> >>> > are
> >>> > correct it returns a resultrow. Thanks for the help.
> >>> >
> >>> > cursor = connection.cursor()
> >>> > cursor.execute("SELECT *  FROM mytable")
> >>> > results_one = cursor.fetchmany(100)
> >>> > for row in results_one:
> >>> > print(type(row))
> >>>
> >>> It's not an issue, when you call SQLAlchemy's fetchmany(), it is
> >>> internally retrieving the pyobc row objects and converting them to
> >>> ResultRow objects.
> >>>
> >>> As I mentioned before, we have three dialects that use Pyodbc and two
> >>> are production quality, same API, same row objects, etc.
> >>>
> >>>
> >>>
> >>> >
> >>> >
> >>> > On Sunday, January 7, 2018 at 12:01:29 PM UTC-6, Mike Bayer wrote:
> >>> >>
> >>> >>
> >>> >>
> >>> >> On Jan 7, 2018 11:29 AM, "Russ Wilson"  wrote:
> >>> >>
> >>> >> When I attempt to create a panda dataframe from the results it
> throws
> >>> >> this
> >>> >> error "Shape of passed values is (1, 100), indices imply (9, 100)"
> >>> >> because
> >>> >> it is seeing the results as 1 column vs a list of columns.  Ill
> take a
> >>> >> look
> >>> >> at the SQL Server one.   Thanks
> >>> >>
> >>> >>
> >>> >> That has nothing to do with a SQLAlchemy dialect because all
> >>> >> SQLAlchemy
> >>> >> result sets come back as a ResultSet object and every row is a
> >>> >> ResultRow.
> >>> >> The pyodbc internals are not exposed.
> >>> >>
> >>> >>
> >>> >>
> >>> >> pd.DataFrame(data=data, columns=column_names)
> >>> >>
> >>> >>
> >>> >>
> >>> >> On Saturday, January 6, 2018 at 11:57:57 PM UTC-6, Mike Bayer wrote:
> >>> >>>
> >>> >>> pyodbc.Row acts like a tuple so there is no special conversion
> >>> >>> needed.
> >>> >>>
> >>> >>> SQLAlchemy has three pyodbc dialects, for SQL Server (very stable),
> >>> >>> MySQL (sorta works), and Sybase (probably doesn't work), but you
> can
> >>> >>> use the first two as examples for the basics.   They base off of
> the
> >>> >>> PyODBCConnector in connectors/pyodbc.py.
> >>> >>>
> >>> >>>
> >>> >>> On Sun, Jan 7, 2018 at 12:40 AM, Russ Wilson 
> >>> >>> wrote:
> >>> >>> >
> >>> >>> > I was attempting to create a new dialect but hit and issue.
> pyodbc
> >>> >>> > is
> >>> >>> > returning a list of pyodbc.Row.  Is there a method i should be
> >>> >>> > implementing
> >>> >>> > to convert the list to a list of tuples.
> >>> >>> >
> >>> >>> > Thanks
> >>> >>> >
> >>> >>> > --
> >>> >>> > SQLAlchemy -
> >>> >>> > The Python SQL Toolkit and Object Relational Mapper
> >>> >>> >
> >>> >>> > http://www.sqlalchemy.org/
> >>>

Re: [sqlalchemy] load_only change in 1.2.0?

2018-01-10 Thread Mike Bayer

On Wed, Jan 10, 2018 at 1:03 PM, Matt Schmidt  wrote:
> I have a small companion library that I wrote for model-to-dict
> serialization. I just updated to 1.2.0 and one of my tests is failing --
>
> https://gitlab.com/sloat/SerialAlchemy/blob/master/tests/test_to_dict.py#L193
>
> In 1.1.x, it worked as expected, but in 1.2, the primary-key is added to the
> serialized profile field. So the output is now:
>
> {
> 'firstname': 'test',
> 'profile': {
> 'id': 3,
> 'somefield': 'somevalue'
> },
> }
>
>
> I just wanted to make sure this is expected behavior from SQLAlchemy. It's
> not a problem for me to update the test, it won't impact the actual
> functionality of the library.

>From making a test (below) I can see this behavior changed within the
1.1 series, not in 1.2. Up through 1.1.13 it gets:

{'_sa_instance_state': , 'data': u'a'}

in 1.1.14 it becomes:

{'_sa_instance_state': , 'data': u'a', 'id': 1}


A bisect brings us to:

https://bitbucket.org/zzzeek/sqlalchemy/issues/4048

this shows there was a bug with undeferral as coming off of a joined
eager load, where the keys to be "undeferred" weren't being acted upon
properly.   pdbing into the example shows that the "id" attribute is
being loaded when load_only() is used, but the bug fixed in 4048 is
preventing the attribute from being populated onto the object
properly.   This implies that the primary key attributes are loaded
even when load_only() seems to exclude them, and that this is the
normally established behavior, here not occurring due to a bug.

This can be confirmed if we change the joinedload to lazyload:

a1 = s.query(A).options(lazyload(A.bs).load_only('data')).one()

print a1.bs[0].__dict__

in an early 1.1 version 1.1.5, we get "id":

{'_sa_instance_state': , 'data': u'a', 'id': 1}

therefore the bug fixed in 4048, while it wasn't testing for this
specific condition, is repairing the deferral rules for attributes
coming off of a joinedload, and under that repair the primary key
attributes are now being populated as would be the case when
load_only() is used in other contexts.

This is not to say that the primary key attributes being present under
load_only() is a desirable feature, only that this is how it seems to
have been working overall, and the change here is repairing a bug that
was masking the attribute from completing its population.

On your end I would recommend making your test not depend on the
primary key attributes being there or not so that changes here won't
break your tests.



from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship("B")


class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey('a.id'))
data = Column(String)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

s.add(A(bs=[B(data='a'), B(data='b')]))
s.commit()

a1 = s.query(A).options(joinedload(A.bs).load_only('data')).one()

print a1.bs[0].__dict__









>
> -Matt
>
> --
> 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.

-- 
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.

[sqlalchemy] Re: load_only change in 1.2.0?

2018-01-10 Thread Jonathan Vanasco



On Wednesday, January 10, 2018 at 2:00:25 PM UTC-5, Matt Schmidt wrote:
>
> The version I upgraded from was 1.1.9, and I originally started the 
> project on 1.1.1. 
>
>
And you wrote that above and I totally missed that line - sorry.  I'll pipe 
down for someone else to chime in now.

-- 
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.

[sqlalchemy] Re: load_only change in 1.2.0?

2018-01-10 Thread Matt Schmidt

The version I upgraded from was 1.1.9, and I originally started the project 
on 1.1.1. 

I saw that bug and thought that was it, but then noticed that was a number 
of versions ago.


On Wednesday, January 10, 2018 at 1:54:54 PM UTC-5, Jonathan Vanasco wrote:
>
> what version did you update from?
>
> If this is the issue I am thinking about, that feature was added a long 
> time ago... 
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3080
>
> the issue in the ticket was the load_only added in the primary keys on 
> joinedload but not subqueryload -- and the orm needs the primary key in 
> order to pull the correct items.  so michael's fix was to include it in 
> subqueryloads automatically.
>
>
> it looks to be 0.9.5 in 2014
>
>
> http://docs.sqlalchemy.org/en/latest/changelog/changelog_09.html#change-25bfed359ea9ffac545b3582739da0c4
>
> and it has been in every release since 1.0b1
>
> if you upgraded from something after 0.9.5, then this may be something 
> else.  if you upgraded from before 0.9.5, this is expected.
>

-- 
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.

[sqlalchemy] Re: load_only change in 1.2.0?

2018-01-10 Thread Jonathan Vanasco

what version did you update from?

If this is the issue I am thinking about, that feature was added a long 
time ago... 

https://bitbucket.org/zzzeek/sqlalchemy/issues/3080

the issue in the ticket was the load_only added in the primary keys on 
joinedload but not subqueryload -- and the orm needs the primary key in 
order to pull the correct items.  so michael's fix was to include it in 
subqueryloads automatically.


it looks to be 0.9.5 in 2014

http://docs.sqlalchemy.org/en/latest/changelog/changelog_09.html#change-25bfed359ea9ffac545b3582739da0c4

and it has been in every release since 1.0b1

if you upgraded from something after 0.9.5, then this may be something 
else.  if you upgraded from before 0.9.5, this is expected.

-- 
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.

[sqlalchemy] load_only change in 1.2.0?

2018-01-10 Thread Matt Schmidt

I have a small companion library that I wrote for model-to-dict 
serialization. I just updated to 1.2.0 and one of my tests is failing --

https://gitlab.com/sloat/SerialAlchemy/blob/master/tests/test_to_dict.py#L193

In 1.1.x, it worked as expected, but in 1.2, the primary-key is added to 
the serialized profile field. So the output is now:

{
'firstname': 'test',
'profile': {
'id': 3,
'somefield': 'somevalue'
},
}


I just wanted to make sure this is expected behavior from SQLAlchemy. It's 
not a problem for me to update the test, it won't impact the actual 
functionality of the library.

-Matt

-- 
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] dialect issue with pyodbc.Row type

Re: [sqlalchemy] load_only change in 1.2.0?

[sqlalchemy] Re: load_only change in 1.2.0?

[sqlalchemy] Re: load_only change in 1.2.0?

[sqlalchemy] Re: load_only change in 1.2.0?

[sqlalchemy] load_only change in 1.2.0?

6 matches

Site Navigation

Mail list logo

Footer information