pyPgSQL Examples - Benjamin Arai

completemiscreantData Management

Nov 28, 2012 (4 years and 10 months ago)

386 views

pyPgSQL Examples

By: Benjamin Arai & Conley Read


Summary



Code Snippet



Insertion Example



Deletion Example



Selection Example


Code Snippet

This code snippet is an example of creating a connection and an execution of a query using pyPgSQL. The connection m
anager function can
specify other parameters, which include username and password. All of the source code and queries are assuming the database
is PostgreSQL
7.4.1 and Python is version 2.3.3. The pyPgSQL package is not currently installed on any of the
lab computers, but is installed on
hebe.cs.ucr.edu.


from pyPgSQL import PgSQL

cx = PgSQL.connect(database=”db_name”)
=
cx.execute(“QUERY”)
=
捸.捬cs攨F
=
=
qh攠捯nn散瑩tn=examp汥ldo敳eno琠tov敲=卑i=捯mm慮ds=you=wou汤=n敥d=瑯=do=楮
-
ord敲=for=汯捫楮g=or=瑲慮s慣t
楯ns=on=the=d慴慢慳攠bu琠楴t楳=楮=
mos琠t慳as=楴⁩i=usu慬ay=only=愠慤d楴楯n慬atwo=or=瑨r敥=數散u瑩ons=of=s瑡tt楮g=慮d=s瑯pp楮g=th攠瑡獫.==
=
=
Insertion Example

This is an insertion into the QuestionVersions table and assumes there is no foreign linking betwee
n tables. There are also ALTER queries that
can be used to modify data in the database.


INSERT INTO QuestionVersions VALUES(



fn瑥t敲
”,
=

fn瑥t敲
”,
=

fn瑥t敲
”,
=

_oo汥慮
”,
=

q數t
”,
=

q數t
”,
=

q數t
”,
=

q數t
”,
=

q數t
”,
=

䑯ub汥
”,
=

q數t

=

=
=
fn瑥t敲
=
=
=
㴠PO
-
b楴⁩i瑥t敲=numb敲.
=
q數t
=
=
㴠q數琠獴物tg=of=v慲楡i汥ls楺攮
=
_oo汥慮
=
=
=
㴠C慮=b攠r数r敳敮瑥t=慳⁡=M⼱=or=瑲u支e慬獥av慬u攮
=
䑯ub汥
=
=
㴠䑯ub汥lpr散楳楯n=numb敲.
=
=
Deletion Example

This is an example of a deletion using the ProfessorID as the only parameter

for deletion. The parameter specified for the column most
correspond with the type specified in the database.


DELETE FROM QuestionVersions WHERE ProfessorID=”
fn瑥t敲
”;
=
=
qh攠mrof敳eorf䐠楳==瑨攠捯汵mn=from=wh楣h=th攠qu敲y=wi汬lm慫攠瑨攠d散楳楯n=on=wh慴a
瑯=d敬整e.==䵵汴楰汥l捯汵mns=捡n=b攠us敤=慦瑥t=th攠
t䡅ob=CiA啓䔠瑯=r敦in攠and⽯r=w楤敮=愠s敡r捨.
=
=
Selection Example


This is a select using the Statement field to find all of the questions that contain the word “tree”.


SELECT * FROM QuestionVersions WHER
E Statement LIKE %
Text
%;


The “%” sign is a wild card for 0 or more items. The “%” sign can be replaced with a “$”, which represents exactly one chara
cter wild card
value and can be specified multiple times in a query.


Other Stuff

These are all simple e
xamples that can be extended to include multiple columns, sub queries and etc. Advanced topics including server
-
side
includes and triggers are covered in the PostgreSQL documentation.