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.com<javascript:> > . > >> 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.
