Re: [sqlalchemy] Mixing matching connectors and dialects in 0.6
Hi, I know this is a long time, but did you ever get SQLAlchemy to successfully work with Vertica ? Please let me know as I am interested in doing the same. Thanks, Femi Anthony On Tuesday, February 2, 2010 11:37:15 AM UTC-5, Bo wrote: Hi Michael, Thanks for the tip on dialect creation; I have a working connection and much of the low level library functionality appears to just work. I've just started digging into the internals to begin implementing introspection support. One issue I've encountered is that PyODBC happens to return boolean columns as strings with values 1 and 0. This confuses the current type system. I have skimmed the TypeDecorator documentation but am still confused about how to associate that with this custom dialect. Any hints? I have attached the dialect base.py and a few test cases (not sure they're useful as Vertica is frustratingly secretive about everything) if you have time to review and comment. On Fri, Jan 15, 2010 at 2:57 PM, Bo Shi bs1...@gmail.com javascript: wrote: That's funny because Oracle and SQL server are utterly, totally different from a SQL quirks perspective. If I were to pick two dialects in SQLA that were *most* different from each other and also non-standard, those would be the two. I was a bit puzzled by this also (granted this was from some early press release I dredged up* from google). I'm still working through their documentation and haven't run across any configuration that might enable a compatability mode yet. * improved compatibility with Oracle and SQLServer SQL dialects http://www.vertica.com/company/news_and_events/Vertica-Analytics-Database-2.5-The-Most-Cost-Effective-Way-To-Modernize-Data-Warehousing create_engine() using 'mssql+pyodbc' seems to work but upon attempting to execute a simple select statement, I get a programming error indicating the following failed to run: 'SELECT user_name() as user_name;' So it seems the dialect is getting some additional state under the hood. lib/sqlalchemy/dialects/mssql/pyodbc.py appears to be hooking PyODBCConnector and MSDialect together. Is it possible to specify another dialect here? If so, is there any documentation on how to do so? you'd want to make yourself a vertica dialect module that imports the PyODBCConnector and uses it as a mixin. I'd suggest copying one of the existing dialects, and probably not the SQL server one unless you know that vertica has a lot of the transact-SQL lineage that SQL server does (the PG and SQLite dialects are the most barebones).To run it, add a setup.py which configures your library as a setuptools entry point, in this case the name would be vertica+pyodbc: from setuptools import setup setup(name=SQLAVertica, description=..., entry_points={ 'sqlalchemy:plugins': ['vertica+pyodbc = mypackage.base:VerticaDialect'] } then using create_engine('vertica+pyodbc://user:pw@host/dbname') will load in your dialect. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlal...@googlegroups.comjavascript: . To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com javascript:. 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 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Mixing matching connectors and dialects in 0.6
Hi Michael, Thanks for the tip on dialect creation; I have a working connection and much of the low level library functionality appears to just work. I've just started digging into the internals to begin implementing introspection support. One issue I've encountered is that PyODBC happens to return boolean columns as strings with values 1 and 0. This confuses the current type system. I have skimmed the TypeDecorator documentation but am still confused about how to associate that with this custom dialect. Any hints? I have attached the dialect base.py and a few test cases (not sure they're useful as Vertica is frustratingly secretive about everything) if you have time to review and comment. On Fri, Jan 15, 2010 at 2:57 PM, Bo Shi bs1...@gmail.com wrote: That's funny because Oracle and SQL server are utterly, totally different from a SQL quirks perspective. If I were to pick two dialects in SQLA that were *most* different from each other and also non-standard, those would be the two. I was a bit puzzled by this also (granted this was from some early press release I dredged up* from google). I'm still working through their documentation and haven't run across any configuration that might enable a compatability mode yet. * improved compatibility with Oracle and SQLServer SQL dialects http://www.vertica.com/company/news_and_events/Vertica-Analytics-Database-2.5-The-Most-Cost-Effective-Way-To-Modernize-Data-Warehousing create_engine() using 'mssql+pyodbc' seems to work but upon attempting to execute a simple select statement, I get a programming error indicating the following failed to run: 'SELECT user_name() as user_name;' So it seems the dialect is getting some additional state under the hood. lib/sqlalchemy/dialects/mssql/pyodbc.py appears to be hooking PyODBCConnector and MSDialect together. Is it possible to specify another dialect here? If so, is there any documentation on how to do so? you'd want to make yourself a vertica dialect module that imports the PyODBCConnector and uses it as a mixin. I'd suggest copying one of the existing dialects, and probably not the SQL server one unless you know that vertica has a lot of the transact-SQL lineage that SQL server does (the PG and SQLite dialects are the most barebones). To run it, add a setup.py which configures your library as a setuptools entry point, in this case the name would be vertica+pyodbc: from setuptools import setup setup(name=SQLAVertica, description=..., entry_points={ 'sqlalchemy:plugins': ['vertica+pyodbc = mypackage.base:VerticaDialect'] } then using create_engine('vertica+pyodbc://user:p...@host/dbname') will load in your dialect. -- 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. base.py Description: Binary data vertica.py Description: Binary data
[sqlalchemy] Mixing matching connectors and dialects in 0.6
Hi All, I'm attempting to get rudimentary support for a Vertica deployment using an ODBC connector. According to their docs, their dialect is mostly compatible with Oracle and SQLServer dialects. create_engine() using 'mssql+pyodbc' seems to work but upon attempting to execute a simple select statement, I get a programming error indicating the following failed to run: 'SELECT user_name() as user_name;' So it seems the dialect is getting some additional state under the hood. lib/sqlalchemy/dialects/mssql/pyodbc.py appears to be hooking PyODBCConnector and MSDialect together. Is it possible to specify another dialect here? If so, is there any documentation on how to do so? -- 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] Mixing matching connectors and dialects in 0.6
Bo Shi wrote: Hi All, I'm attempting to get rudimentary support for a Vertica deployment using an ODBC connector. According to their docs, their dialect is mostly compatible with Oracle and SQLServer dialects. That's funny because Oracle and SQL server are utterly, totally different from a SQL quirks perspective. If I were to pick two dialects in SQLA that were *most* different from each other and also non-standard, those would be the two. create_engine() using 'mssql+pyodbc' seems to work but upon attempting to execute a simple select statement, I get a programming error indicating the following failed to run: 'SELECT user_name() as user_name;' So it seems the dialect is getting some additional state under the hood. lib/sqlalchemy/dialects/mssql/pyodbc.py appears to be hooking PyODBCConnector and MSDialect together. Is it possible to specify another dialect here? If so, is there any documentation on how to do so? you'd want to make yourself a vertica dialect module that imports the PyODBCConnector and uses it as a mixin. I'd suggest copying one of the existing dialects, and probably not the SQL server one unless you know that vertica has a lot of the transact-SQL lineage that SQL server does (the PG and SQLite dialects are the most barebones).To run it, add a setup.py which configures your library as a setuptools entry point, in this case the name would be vertica+pyodbc: from setuptools import setup setup(name=SQLAVertica, description=..., entry_points={ 'sqlalchemy:plugins': ['vertica+pyodbc = mypackage.base:VerticaDialect'] } then using create_engine('vertica+pyodbc://user:p...@host/dbname') will load in your dialect. -- 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] Mixing matching connectors and dialects in 0.6
That's funny because Oracle and SQL server are utterly, totally different from a SQL quirks perspective. If I were to pick two dialects in SQLA that were *most* different from each other and also non-standard, those would be the two. I was a bit puzzled by this also (granted this was from some early press release I dredged up* from google). I'm still working through their documentation and haven't run across any configuration that might enable a compatability mode yet. * improved compatibility with Oracle and SQLServer SQL dialects http://www.vertica.com/company/news_and_events/Vertica-Analytics-Database-2.5-The-Most-Cost-Effective-Way-To-Modernize-Data-Warehousing create_engine() using 'mssql+pyodbc' seems to work but upon attempting to execute a simple select statement, I get a programming error indicating the following failed to run: 'SELECT user_name() as user_name;' So it seems the dialect is getting some additional state under the hood. lib/sqlalchemy/dialects/mssql/pyodbc.py appears to be hooking PyODBCConnector and MSDialect together. Is it possible to specify another dialect here? If so, is there any documentation on how to do so? you'd want to make yourself a vertica dialect module that imports the PyODBCConnector and uses it as a mixin. I'd suggest copying one of the existing dialects, and probably not the SQL server one unless you know that vertica has a lot of the transact-SQL lineage that SQL server does (the PG and SQLite dialects are the most barebones). To run it, add a setup.py which configures your library as a setuptools entry point, in this case the name would be vertica+pyodbc: from setuptools import setup setup(name=SQLAVertica, description=..., entry_points={ 'sqlalchemy:plugins': ['vertica+pyodbc = mypackage.base:VerticaDialect'] } then using create_engine('vertica+pyodbc://user:p...@host/dbname') will load in your dialect. -- 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.