Tag: PostgreSQL

Access PostgreSQL from Python using pscopg2

psycopg2 is a python module for PostgreSQL.

  • Basic module usage

http://initd.org/psycopg/docs/usage.html#basic-module-usage

  • with statement

Starting from version 2.5, psycopg2’s connections and cursors are context managers and can be used with the with statement:

http://initd.org/psycopg/docs/usage.html#with-statement

  • Server side cursors

http://initd.org/psycopg/docs/usage.html#server-side-cursors

When a database query is executed, the Psycopg cursor usually fetches all the records returned by the backend, transferring them to the client process. If the query returned an huge amount of data, a proportionally large amount of memory will be allocated by the client.

If the dataset is too large to be practically handled on the client side, it is possible to create a server side cursor. Using this kind of cursor it is possible to transfer to the client only a controlled amount of data, so that a large dataset can be examined without keeping it entirely in memory.

 

Posts Referenced:

 

PostgreSQL — column names of a table are case-sensitive

All identifiers (including column names) that are not double-quoted are converted to lower case in PostgreSQL. Column names that were created with double-quotes and thereby retained upper-case letters (and/or other syntax violations) have to be double-quoted for the rest of their life. So, PostgreSQL column names are case-sensitive:

SELECT * FROM people WHERE "first_Name" = 'xyz';

Note that 'xyz' does not need to be double-quoted. Values (string literals) are enclosed in single quotes.

If you try to do this query ( first_Name is a column in the table people

SELECT * FROM people WHERE first_Name='xyz';

And you will meet this error

ERROR: column “first_Name” does not exist

 

Advice is to use legal, lower-case names exclusively so double-quoting is not needed.

 

(Thanks, Scott, for some hints.)

 

References:

http://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive