connecting to server process via sockets
For fun and learning, I would like to connect to the Postgresql backend
and issue queries using sockets. I'm using Python's socket module. I'm
new to socket programming, but I'm experienced with Python and
Postgresql. I've been using the JDBC driver and the online
documentation as a guide, but I'm afraid my ignorance has led me to
failure thus far.
This is what I think I understand.
1. Send the startup message as such.
a. length of message.
b. protocol major (3)
c. protocol minor (0) (don't know what this is).
d. message
e. send 0 (Don't know why?)
O.K. Here I show my ignorance.
#!/usr/bin/python
import socket
# Connection string
cnstring = 'user=randall, database=dws, client_encoding=UNICODE,
DateStyle=ISO' # This just wrapped in my email.
msg_len = str(len(cnstring))
protocol_major = '3'
protocol_minor = '0'
pgsocket = socket.socket()
pgsocket.connect(('localhost', 5432))
pgsocket.send(msg_len)
pgsocket.send(protocol_major)
pgsocket.send(protocol_minor)
pgsocket.send(cnstring)
pgsocket.send('0')
pgsocket.close()
When I run this, this is what shows up in the logs.
2005-05-16 10:11:34 [2638] LOG: connection received: host=127.0.0.1
port=42607
2005-05-16 10:11:34 [2638] LOG: invalid length of startup packet
Please do not recommend that I use an existing API. I'm doing this for
fun and maybe to come up with a simple pure python database driver.
Randall
On May 16, 2005, at 11:14 AM, Randall Smith wrote:
When I run this, this is what shows up in the logs.
2005-05-16 10:11:34 [2638] LOG: connection received: host=127.0.0.1
port=42607
2005-05-16 10:11:34 [2638] LOG: invalid length of startup packet
We just had a thread about this on the Novice (!) list. It should
answer your question about what the startup packet needs to look like.
http://archives.postgresql.org/pgsql-novice/2005-05/msg00130.php
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
Randall Smith <randall@tnr.cc> writes:
For fun and learning, I would like to connect to the Postgresql
backend and issue queries using sockets. I'm using Python's socket
module. I'm new to socket programming, but I'm experienced with
Python and Postgresql. I've been using the JDBC driver and the online
documentation as a guide, but I'm afraid my ignorance has led me to
failure thus far.
[...]
No direct help, but a couple of suggestions:
1) Take a look at pg-dot-lisp, which does the same thing you are
trying to do, but in Lisp. You might find it more readable than
the JDBC driver. I am not sure which protocol versions it
supports, though. It's at:
http://www.chez.com/emarsden/downloads/
2) Write a simple libpq app (or use one of the existing Python
interfaces linked against libpq) and watch the session with
Etherial or tcpdump. That'll show you the differences between your
failing app and a working connection...
-Doug
Randall Smith wrote:
For fun and learning, I would like to connect to the Postgresql backend
and issue queries using sockets. I'm using Python's socket module. I'm
new to socket programming, but I'm experienced with Python and
Postgresql.
Look in the postgres docs for the section on frontend/backend protocol.
The handshaking and format of the bytes that go across the socket
are explained in that doc. section.
You also might want to look at:
http://ca.geocities.com/zazu@rogers.com/pgsqueak/
where there are links to a class diagram and state diagram
of an implementation done in Squeak Smalltalk. I don't know
Python, but IMHO the basic design should be transferable.
Note that frontend/backend protocol version 2 is implemented
in that code, and the current docs will reflect version 3
(so you'd have to look at the older docs to match up the code).
Hope that helps.
--yanni