[sqlalchemy] Re: _compose_select_body
I figured it out...thanks On Saturday, January 13, 2018 at 9:35:29 AM UTC-6, Russ Wilson wrote: > > > in the _compose_select_body within in compiler.py it adds [] around the > various parts of the select. I need to alter that so it puts quotes. Is > there a property I can set to change that behavior or do i need to > overwrite it? > > Thanks for the help! > -- 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] _compose_select_body
in the _compose_select_body within in compiler.py it adds [] around the various parts of the select. I need to alter that so it puts quotes. Is there a property I can set to change that behavior or do i need to overwrite it? Thanks for the help! -- 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
Thanks for the insights
On Tue, Jan 9, 2018 at 10:23 PM Mike Bayer <mike...@zzzcomputing.com> wrote:
> On Tue, Jan 9, 2018 at 8:45 PM, Russ Wilson <rpwil...@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.
>
>
>
>
> >
> > 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" <rpwi...@gmail.com> 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 <mik...@zzzcomputing.com>
> wrote:
> >>>
> >>> 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.
> >>>
> >>>
> >>>
> >>> >
> >>> >
> >>> > On Sunday, January 7, 2018 at 12:01:29 PM UTC-6, Mike Bayer wrote:
> >>> >>
> >>> >>
> >>> >>
> >>> >> 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.
> >>&
Re: [sqlalchemy] dialect issue with pyodbc.Row type
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" <rpwi...@gmail.com >
> 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 <mik...@zzzcomputing.com
> > wrote:
>
>> 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.
>>
>>
>>
>> >
>> >
>> > On Sunday, January 7, 2018 at 12:01:29 PM UTC-6, Mike Bayer wrote:
>> >>
>> >>
>> >>
>> >> 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.
>> >>
>> >>
>> >>
>> >> 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 <rpwi...@gmail.com>
>> 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
Re: [sqlalchemy] dialect issue with pyodbc.Row type
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 <mike...@zzzcomputing.com> wrote:
> On Sun, Jan 7, 2018 at 9:07 PM, Russ Wilson <rpwil...@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.
>
>
>
> >
> >
> > On Sunday, January 7, 2018 at 12:01:29 PM UTC-6, Mike Bayer wrote:
> >>
> >>
> >>
> >> 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.
> >>
> >>
> >>
> >> 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 <rpwi...@gmail.com>
> 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 grou
Re: [sqlalchemy] dialect issue with pyodbc.Row type
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" <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.
>
>
>
> 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 <rpwi...@gmail.com> 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
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 <rpwi...@gmail.com > > 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] dialect issue with pyodbc.Row type
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.
