dialect issue with pyodbc.Row type

[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

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

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

[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)

[Mike Bayer]

On Jan 7, 2018 11:29 AM, "Russ Wilson" <rpwi...@gmail.com> 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.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe@googlegroups.com.

[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))

[Mike Bayer]

On Sun, Jan 7, 2018 at 9:07 PM, Russ Wilson <rpwi...@gmail.com> 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.

[Russ Wilson]

Is there a good doc that covered at at min needs to be extended to create a dialect? 

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/wtLwkQr2xSY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

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

> email to sqlalchemy+unsubscribe@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 a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/wtLwkQr2xSY/unsubscribe.

To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@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+unsubscribe@googlegroups.com.

[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()

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/wtLwkQr2xSY/unsubscribe.

To unsubscribe from this group and all its topics, 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.

[Mike Bayer]

On Tue, Jan 9, 2018 at 8:45 PM, Russ Wilson <rpwi...@gmail.com> 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.

[Russ Wilson]

Thanks for the insights