Elegant Data: August 2018

In this post, I want talk about three things, first, how to create a password for a postgres user. Second, how to load a csv into a sample database and finally how to access this table using python/psycopg package.
Setup password for Postgres user:
In my previous post, I created a user krishna in Postgres but I haven't come across during installation a password setup step. Quick search in StackOverflow showed this approach

$psql
psql (10.4 (Ubuntu 10.4-0ubuntu0.18.04))
Type "help" for help.

krishna=# \password
Enter new password: 
Enter it again: 
krishna=#

Load csv into a Postgres database.
Found this example of postgres site, it has details of land sales in the UK, going back several decades, and is 3.5GB as of August 2016,click here for the file

-- Executing query:
CREATE TABLE land_registry_price_paid_uk(
  transaction uuid,
  price numeric,
  transfer_date date,
  postcode text,
  property_type char(1),
  newly_built boolean,
  duration char(1),
  paon text,
  saon text,
  street text,
  locality text,
  city text,
  district text,
  county text,
  ppd_category_type char(1),
  record_status char(1));
Query returned successfully with no result in 273 msec.

-- Executing query:
COPY land_registry_price_paid_uk FROM '/home/krishna/Downloads/pp_100k.csv' 
with (format csv, encoding 'win1252', header false, null '', quote '"', force_null (postcode, saon, paon, street, locality, city, district));
Query returned successfully: 100000 rows affected, 1.1 secs execution time.

Install psycopg2 pip install psycopg2

$ pip install psycopg2
Collecting psycopg2
  Downloading https://files.pythonhosted.org/packages/7c/e6/d5161798a5e8900f24216cb730f2c2be5e4758a80d35c8588306831c0c99/psycopg2-2.7.5-cp27-cp27mu-manylinux1_x86_64.whl (2.7MB)
    100% |████████████████████████████████| 2.7MB 316kB/s 
Installing collected packages: psycopg2
Successfully installed psycopg2-2.7.5

Example call from python

#!/usr/bin/python
import psycopg2
import sys
import pprint
 
def main():
	conn_string = "host='localhost' dbname='oflc' user='krishna' password='*****'"
	# print the connection string we will use to connect
	print "Connecting to database\n	-&gt;%s" % (conn_string)
 
	# get a connection, if a connect cannot be made an exception will be raised here
	conn = psycopg2.connect(conn_string)
 
	# conn.cursor will return a cursor object, you can use this cursor to perform queries
	cursor = conn.cursor()
 
	# execute our Query
	cursor.execute("select * from land_registry_price_paid_uk limit 10")
 
	# retrieve the records from the database
	records = cursor.fetchall()
 
	# print out the records using pretty print
	# note that the NAMES of the columns are not shown, instead just indexes.
	# for most people this isn't very useful so we'll show you how to return
	# columns as a dictionary (hash) in the next example.
	pprint.pprint(records)
 
if __name__ == "__main__":
	main()

Elegant Data

Friday, August 10, 2018

Reset password for postgres user and load sample database