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?

score 114 · Answer 1 · answered Mar 03 '17 at 19:33

114

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.

answered Mar 03 '17 at 19:33

andrew

4,991
5
24
27

8

I have another question about `postgresql+psycopg2://user:password@...`, why someone articles say only use `postgresql://user:password@...`, what's the difference? – Tony Chou Nov 06 '20 at 13:52
According to this comment it is the same: https://stackoverflow.com/a/47946916/7775043 – Albin Feb 19 '21 at 11:18
2

@TonyChou see https://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." – AlexK Mar 20 '21 at 06:00

score 68 · Accepted Answer · edited Jul 18 '16 at 10:50

68

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

edited Jul 18 '16 at 10:50

manurajhada

5,284
3
24
43

answered Feb 19 '12 at 22:19

Uku Loskit

40,868
9
92
93

2

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. – swade May 16 '18 at 19:18
17

@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. – jjmontes Jan 02 '19 at 12:08

score 3 · Answer 3 · edited Jan 02 '18 at 13:36

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

edited Jan 02 '18 at 13:36

Vitaly Migunov

4,297
2
19
23

answered Jan 02 '18 at 11:36

George Joubran Abu Dauod

31
1

Requires pg_config, which may not be present for everyone. I'm working in Docker, and it was not in my case. – MrChadMWood Jun 30 '23 at 23:29

Connecting postgresql with sqlalchemy

3 Answers3

Linked