使用 SQLAlchemy 连接到 Vertica 数据库 [英] Connecting to Vertica database using SQLAlchemy

问题描述

我正在尝试使用 SQLAlchemy 连接到 Vertica 数据库.

I'm trying to connect to a Vertica database using SQLAlchemy.

我在 https://github.com/jamescasbon/vertica-sqlalchemy 遇到并安装了 Vertica 方言.我还安装了pyodbc.

I came across and installed a Vertica dialect at https://github.com/jamescasbon/vertica-sqlalchemy. I've also installed pyodbc.

在http://www.pythoncentral.io/sqlalchemy-orm-使用基本教程示例/，我有以下代码片段:-

Using a basic tutorial at http://www.pythoncentral.io/sqlalchemy-orm-examples/, I have the following code snippet :-

from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, func
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True)
    name = Column(String)

engine = create_engine(sa.engine.url.URL(drivername='vertica+pyodbc',
       username='<username>',password='<password>',
       host='<host>',database='<db name>',))


session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

当它运行时，我得到了回溯:-

When this runs I get the traceback :-

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/schema.py", line 3291, in    create_all
tables=tables)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1546, in _run_visitor
with self._optional_conn_ctx_manager(connection) as conn:
File "/usr/lib/python2.7/contextlib.py", line 17, in __enter__
return self.gen.next()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1539, in    _optional_conn_ctx_manager
with self.contextual_connect() as conn:
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1729, in contextual_connect
self.pool.connect(),
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 332, in connect
return _ConnectionFairy._checkout(self)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 626, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 433, in checkout
rec = pool._do_get()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 945, in _do_get
return self._create_connection()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 278, in _create_connection
return _ConnectionRecord(self)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 404, in __init__
self.connection = self.__connect()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 527, in __connect
connection = self.__pool._creator()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line 95, in connect
connection_invalidated=invalidated
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line 89, in connect
return dialect.connect(*cargs, **cparams)
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 376, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)') None None

我猜最后一行是要查看的:--

The last line I guess is the one to look at :--

sqlalchemy.exc.DBAPIError: (Error) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)') None None

可以从其他客户端连接到数据库，因此凭据等没问题.

Connections can be made to the database from other clients so the credentials etc are fine.

我不确定是什么原因造成的 - 谁能帮忙?

I'm not sure what is causing this - can anyone help?

提前致谢！

推荐答案

SQLAlchemy 正在使用 unixODBC 连接到 Vertica.您需要 安装驱动程序和设置 DSN

SQLAlchemy is using unixODBC to connect to Vertica. You need to install the drivers and set up a DSN

您应该能够使用这些参数进行连接.这在我之前的 SQLAlchemy/Vertica 项目中对我有用.另外，如果这不起作用，我会确保它配置正确，并且您可以使用 isql(带有 unixODBC)进行连接.

You should be able to connect with these parameters. This is what worked for me in my previous SQLAlchemy / Vertica project. Also, if this doesn't work, I would make sure it is configured properly and that you can connect using isql (comes with unixODBC).

drivername='vertica+pyodbc',
username='myuser',
password='mypassword',
host='hostname',
database='DBNAME',

您也可以为 DSN 连接执行此操作:

You can also do this for a DSN connection:

engine = create_engine('vertica+pyodbc://username:password@mydsn')

这篇关于使用 SQLAlchemy 连接到 Vertica 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！