Class03 Answer:

Write/Run a simple Psycopg 2 script

I used Google to find the Psycopg 2 documentation and then I wrote a script:


"""
psy10.py

psycopg2 demo
ref:
http://initd.org/psycopg/docs/usage.html

if ModuleNotFoundError,
Useful shell syntax:
conda install psycopg2
"""

import psycopg2

# Connect to an existing database
conn = psycopg2.connect("dbname=ann password=ann user=ann host=127.0.0.1")
# Open a cursor to perform database operations
cur = conn.cursor()

# Execute a command: this creates a new table
cur.execute("CREATE TABLE IF NOT EXISTS test (id serial PRIMARY KEY, num integer, data varchar);")

# Pass data to fill a query placeholders and let Psycopg perform
# the correct conversion (no more SQL injections!)
cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",(100, "abc'def"))

# Query the database and obtain data as Python objects
cur.execute("SELECT * FROM test;")
print(cur.fetchone())

# Make the changes to the database persistent
conn.commit()

# Close communication with the database
cur.close()
conn.close()

'bye'

I ran the above script and captured screen output:


dan@a78:~/ml4/public/class03demos $ python psy10.py 
Traceback (most recent call last):
  File "psy10.py", line 7, in <module>
    import psycopg2
ModuleNotFoundError: No module named 'psycopg2'
dan@a78:~/ml4/public/class03demos $ 
dan@a78:~/ml4/public/class03demos $ 
dan@a78:~/ml4/public/class03demos $ 



dan@a78:~/ml4/public/class03demos $ conda install psycopg2
Fetching package metadata .........
Solving package specifications: .

Package plan for installation in environment /home/dan/anaconda3:

The following NEW packages will be INSTALLED:

    libpq:    9.5.4-0      
    psycopg2: 2.7.1-py36_0 

The following packages will be UPDATED:

    conda:    4.3.21-py36_0 --> 4.3.22-py36_0

Proceed ([y]/n)? 

libpq-9.5.4-0. 100% || Time: 0:00:00   2.79 MB/s
psycopg2-2.7.1 100% || Time: 0:00:00   8.44 MB/s
conda-4.3.22-p 100% || Time: 0:00:00   9.28 MB/s
dan@a78:~/ml4/public/class03demos $ 
dan@a78:~/ml4/public/class03demos $ 
dan@a78:~/ml4/public/class03demos $ 



dan@a78:~/ml4/public/class03demos $ python psy10.py 
(1, 100, "abc'def")
dan@a78:~/ml4/public/class03demos $ 
dan@a78:~/ml4/public/class03demos $ 
dan@a78:~/ml4/public/class03demos $

Class03 Lab


ml4.us About Blog Contact Class01 Class02 Class03 Class04 Class05 Class06 Class07 Class08 Class09 Class10 dan101 Forum Google Hangout Vboxen