Connecting postgresql with sqlalchemy

Question

I know this might be really a simple question but I don't know the solution. What is happening here when I try to connect to postgresql? I am self learner in this field of database and programming so please be gentle with me. When I try following code:

import sqlalchemy
db = sqlalchemy.create_engine('postgresql:///tutorial.db')

I get this error:

Traceback (most recent call last): File "", line 1, in db = sqlalchemy.create_engine('postgresql:///tutorial.db') File "C:\Python27\lib\site-packages\sqlalchemy-0.7.5dev-py2.7.egg\sqlalchemy\engine__init__.py", line 327, in create_engine return strategy.create(*args, **kwargs) File "C:\Python27\lib\site-packages\sqlalchemy-0.7.5dev-py2.7.egg\sqlalchemy\engine\strategies.py", line 64, in create dbapi = dialect_cls.dbapi(**dbapi_args) File "C:\Python27\lib\site-packages\sqlalchemy-0.7.5dev-py2.7.egg\sqlalchemy\dialects\postgresql\psycopg2.py", line 289, in dbapi psycopg = import('psycopg2') ImportError: No module named psycopg2

Do I need to install psycopg2 separately? What is the correct connection string for postgresql?

Solution 1

Yes, psycopg2 are basically the Python drivers for PostgreSQL that need to be installed separately.

A list of valid connection strings can be found here, yours is a bit off (you need to the username, the password and hostname as specified in the link below):

http://docs.sqlalchemy.org/en/latest/core/engines.html#postgresql

Solution 2

You would need to pip install SQLAlchemy and pip install psycopg2. An example of a SQLAlchemy connection string that uses psycopg2:

from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://user:password@hostname/database_name')

You could also connect to your database using the psycopg2 driver exclusively:

import psycopg2
conn_string = "host='localhost' dbname='my_database' user='postgres' password='secret'"
conn = psycopg2.connect(conn_string)

However, using the psycopg2 driver to connect does not take advantage of SQLAlchemy.

Solution 3

Yes, you need to install psycopg2 separately, if you're using linux you can simply enter the following line to the terminal: $pip install psycopg2 if this doesn't work try using sudo: $sudo pip install psycopg2

Comments

[swade]

I'm honestly baffled that this is not a dependency of SQLAlchemy. If you are going to wrap functionality of another package with yours, you should support it and not require a separate installation.

[jjmontes]

@StevenWade not really, you don't usually want to install dependencies for all database systems when you are going to use possibly only one. That's why dependencies in this kind of packages are optional, and not enforced.

[Tony Chou]

I have another question about postgresql+psycopg2://user:password@..., why someone articles say only use postgresql://user:password@..., what's the difference?

[Albin]

According to this comment it is the same: stackoverflow.com/a/47946916/7775043

[AlexK]

@TonyChou see docs.sqlalchemy.org/en/14/core/engines.html: "The PostgreSQL dialect uses psycopg2 as the default DBAPI", so there is no difference. " DBAPI is a 'low level' API which is typically the lowest level system used in a Python application to talk to a database."