5

I am trying to connect to PostgreSQL using Python and am running into lots of problems. My os is windows 7, I am using postgresql 9.3, and python 3.3 and 3.4.

I first tried to this using psycopg2, using the following code 

try:
    conn = psycopg2.connect("dbname='ravi' user='potsgres' host='localhost' password='***'")
except:
    print ("I am unable to connect to the database")

Next I tried to connect using pyodbc using this code

try:
    connection = pyodbc.connect("DRIVER={psqlOBDC};SERVER=localhost:5432;DATABASE=ravi;UID=postgres;PWD=***")
except:
    print("not working")

And no connection was made.

I have already tried several things. I have gone into environment variables to make sure there were paths for postgresql and python.I tried connecting to a SQL server database that I have already created using pyodbc and was able to connect without any problems. I have gone into the ODBC Data Source Administrator to test the PostgreSQL ODBC driver that I had installed, and the test says the connection was succesful. I'm not sure if this is relevant, but I can connect to the postgresql database without problems using geoserver. I'm not sure if it was an ODBC, JDBC or some other type of connection. Also, I originally tried to make the connection using PHP, but I know very little about PHP, so I assumed there was something wrong in my code.

Any ideas on what the problem could be? A couple of avenues I thought of but couldn't figure out if there was a way to see in detail what the error stopping the connection was. Also, I tried connecting to the database using the command line, but the command line doesn't let me enter the password.

I have found a couple of potential solutions but I either didn't understand exactly what they meant, or applied to a linux environment and I didn't know what that meant in windows terms

Opening a postgres connection in psycopg2 causes python to crash

Connecting to PostgreSQL using pyodbc

I've only just started to learn how to code, so it would be really appreciated if any potential solutions were kept as simple as possible, and assumed I know next to nothing about computers. Thanks a lot for any help!

EDIT: Using univerio's example here's the error message when I try to use psycopg2 I get the following error SyntaxError: invalid syntax but I can't see where I messed up the syntax.

If I try to use pyODBC here's the error I get ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I went into the ODBC Data Source Administrator and changed the SSL to require and allow. Neither helped. Also, the name of the driver is actually PostgreSQL35W. Adding 35W did not change the error message.

Community
  • 1
  • 1
Ravi Mehta
  • 485
  • 1
  • 6
  • 15
  • Well instead of catching exceptions and ignoring them, how about printing out the exception? – univerio Oct 16 '14 at 20:15
  • How do you print an exception? When I put in the code for the connection without using the try: exception: format, python acts as if the code was successful but I start getting error when I start trying to actually do something with the connection. – Ravi Mehta Oct 16 '14 at 20:32
  • `except Exception as e: print(e)` Also python should crash if you had an exception but didn't catch it. – univerio Oct 16 '14 at 20:36
  • If that is really your password in there: change it NOW. – RickyA Oct 16 '14 at 20:38
  • user='potsgres'? -> user='postgres' – RickyA Oct 16 '14 at 20:39
  • When I put in the code suggested by univerio I get the following error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') – Ravi Mehta Oct 16 '14 at 20:45
  • @RickyA - No, I have real passwords. Just didn't want to show them to everyone – Ravi Mehta Oct 16 '14 at 20:46
  • You are aware that we can see the edit history... – RickyA Oct 16 '14 at 20:58
  • I know, I also went and changed the password to something different. Thanks for reminding me though. Wouldn't have realized I just showed the whole world the password otherwise. – Ravi Mehta Oct 16 '14 at 20:59
  • @RaviMehta Can you print the exception from psycopg2, not pyodbc? There's no reason to be using pyodbc. – univerio Oct 17 '14 at 01:55
  • Actually, there was a real syntax error in the comment above. Here's the actual error: – Ravi Mehta Oct 17 '14 at 13:59
  • @RaviMehta That's the connection object, not the exception. – univerio Oct 17 '14 at 17:34
  • Yes, I realized that now. That was a stupid mistake on my part. Thanks a lot. – Ravi Mehta Oct 17 '14 at 17:40

1 Answers1

0

psycopg2 takes keyword arguments, not a single string, as per the other question you linked to. Just take the double quotes out.

jennykwan
  • 2,631
  • 1
  • 22
  • 33