Python and Postgresql

Question

If you want to manipulate the data in a table in a postgresql database using some python (perhaps by slightly analyzing the result set with scipy), and then want to export this data back to another table in the same database, as you could do an implementation ?

Is this the only / best way to do this, just to run the query, save it in a python array, manipulate the array in python, and then run another sql statement to output to the database?

I am really asking if there is a more efficient way to process the data?

Thanks Ian

Answer 1

You can use PL / Python to write PostgreSQL functions for data management.

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

Despite the fact that tbh, I think this is a large part of the fly compared to processing it in an external client in most cases.

Answer 2

I'm not sure I understand what you mean, but I would say it sounds a lot like

INSERT INTO anothertable SELECT stuff FROM the_table RETURNING * 

and then work with the returned rows. That is, of course, if you do not want to change the data when manipulating it.

Answer 3

This is the only / best way to do this for just run the query, do I have python store it in an array, manipulate the array in python, and then run another sql to output to the database?

Not the only way (see other answers), but IMHO is the best and, of course, the easiest. This requires only the PostgreSQL library (I use psycopg ). The standard interface is described in PEP 249 .

SELECT example with psycopg:

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

and INSERT :

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

Answer 4

pgnumpy seems to be what you are looking for.

Answer 5

I would consider using http://www.sqlalchemy.org/ .

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

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

Answer 6

You can use ORM, such as SQLAlchemy , to get data into an “object” and manipulate that object. Such an implementation is likely to be more elegant than just using an array.

Answer 7

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