Python and Postgresql

Question

Hi all,

if you wanted to manipulate the data in a table in a postgresql database using some python (maybe running a little analysis on the result set using scipy) and then wanted to export that data back into another table in the same database, how would you go about the implementation?

Is the only/best way to do this to simply run the query, have python store it in an array, manipulate the array in python and then run another sql statement to output to the database?

I'm really just asking, is there a more efficient way to deal with the data?

Thanks, Ian

Answer 1

I'd think about using http://www.sqlalchemy.org/.

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

It supports postgres, too - http://www.sqlalchemy.org/docs/05/reference/dialects/postgres.html

Answer 2

You could use PL/Python to write a PostgreSQL function to manipulate the data.

http://www.postgresql.org/docs/current/static/plpython.html

Although tbh I think it's much of a muchness compared to processing it in an external client for most cases.

Answer 3

Using an ORM like SQLAlchemy can abstract away the low level SQL stuff. Whether this is more "efficient" is debatable; it should certainly make things easier to code, though.

Answer 4

I'm not sure I understand what you mean, but I'd say it sounds very much like

INSERT INTO anothertable SELECT stuff FROM the_table RETURNING *

and then work on the returned rows. That is, of course, if you don't want to modify the data when you manipulate it.

Answer 5

You could use an ORM such as SQLAlchemy to retrieve the data into an "object", and manipulate that object. Such an implementation would probably be more elegant than just using an array.

Answer 6

I agree with the SQL Alchemy suggestions or using Django's ORM. Your needs seem to simple for PL/Python to be used.

Answer 7

Is the only/best way to do this to simply run the query, have python store it in an array, manipulate the array in python and then run another sql statement to output to the database?

Not the only way (see the other answers) but IMHO the best and certainly the simplest. It just requires a PostgreSQL librray (I use psycopg). The standard interface is documented in PEP 249.

An example of a SELECT with psycopg:

cursor.execute("SELECT * FROM students WHERE name=%(name)s;", 
               globals())

and an INSERT:

cursor.execute("INSERT INTO Foobar (t, i) VALUES (%s, %s)", 
               ["I like Python", 42])