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

2018-01-09 Thread Mike Bayer 
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/
>>> >>> >
>>> >>> > 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

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

2018-01-09 Thread Russ Wilson 
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()

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/
>> >>> >
>> >>> > 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+...@googlegroups.com.
>> >>> > To post to this group, send email to sqlal...@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

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

2018-01-09 Thread Mike Bayer 
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/
> >>> >
> >>> > 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+...@googlegroups.com.
> >>> > To post to this group, send email to sqlal...@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+...@googlegroups.com.
> >> To post to this group, send email to sqlal...@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: [sqlalchemy] dialect issue with pyodbc.Row type

2018-01-09 Thread Russ Wilson 
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/
> >>> >
> >>> > 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+...@googlegroups.com.
> >>> > To post to this group, send email to sqlal...@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+...@googlegroups.com.
> >> To post to this group, send email to sqlal...@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 -
> 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

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

2018-01-08 Thread Mike Bayer 
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/
>>> >
>>> > 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+...@googlegroups.com.
>>> > To post to this group, send email to sqlal...@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+...@googlegroups.com.
>> To post to this group, send email to sqlal...@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 - 
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

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

2018-01-07 Thread Russ Wilson 
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))


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/ 
>> > 
>> > 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+...@googlegroups.com. 
>> > To post to this group, send email to sqlal...@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+...@googlegroups.com .
> To post to this group, send email to sqlal...@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.

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

2018-01-07 Thread Mike Bayer 
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/
> >
> > 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+...@googlegroups.com.
> > To post to this group, send email to sqlal...@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 - 
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

2018-01-07 Thread Russ Wilson 
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

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/ 
> > 
> > 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+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@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.

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

2018-01-06 Thread Mike Bayer 
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/
>
> 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] dialect issue with pyodbc.Row type

2018-01-06 Thread Russ Wilson 

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/

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.