Python Learning notes-use of PostgreSQL

First, install the PostgreSQL module

Pip Install PSYCOPG2

Sometimes it will fail, install more than 2 times (I succeeded the second time).

Second, the database connection interface

Because Python unifies the interface of the database connection, PSYCOPG2 and MySQLdb are similar in the way they are used:

Pymysql. Connect () parameter description Host (str):      MySQL server address port (int):      MySQL server port number user (str):      username  password(str):  Password database (str):   A method supported by the DB Name Connection object cursor ()        uses the connection to create and return a cursor commit ()        Commits the current transaction rollback ()      rolls back the current transaction close ()         closes the connection cursor object supported methods Execute (OP)     executes a database query command Fetchone ()      Get the next line of the result set Fetchmany (size) Gets the next few rows of the result set Fetchall ()      gets all the rows in the result sets rowcount ()      returns the number of data bars or affects the number of rows close ()         closes the cursor object

Iii. examples

MySQL Script

--------------------------------table structure for account------------------------------DROP table IF EXISTS ' Account '; CREATE TABLE ' account '  (  ' acctid ' varchar () CHARACTER SET UTF8 COLLATE utf8_general_ci not NULL,  ' name ' var char (CHARACTER) SET UTF8 COLLATE utf8_general_ci null default NULL,  ' money ' decimal (0) null default NULL) Engin E = InnoDB CHARACTER SET = UTF8 COLLATE = utf8_general_ci row_format = Dynamic;--------------------------------Records o F Account------------------------------insert in ' account ' values (' 1 ', ' Zhang San ', ' + '), insert INTO ' account ' values (' 2 ', ' Lee Four ', 150);

Python program

#   Coding:utf8import Sys
Import PSYCOPG2 #PostgreSQL

Class TransferMoney (object): Def __init__ (self, conn): Self.conn = conn def check_acct_available (self, Acctid            ): cursor = self.conn.cursor () try:sql = "SELECT * from account where acctid= '%s '"% Acctid  Print ("check_acct_available:" + sql) cursor.execute (sql) rs = Cursor.fetchall () if Len (rs)! = 1:raise Exception ("account%s does not exist"% Acctid) finally:cursor.close () def Has_eno Ugh_money (self, Acctid, money): cursor = self.conn.cursor () try:sql = "SELECT * From Account whe Re acctid= '%s ' and money>%s "% (Acctid, money) print (" Has_enough_money: "+ sql) c Ursor.execute (sql) rs = Cursor.fetchall () If Len (rs)! = 1:raise Exception ("account%s does not have enough Amount "% Acctid) finally:cursor.close () def reduce_money (self, Acctid, money): cursor = Self.con  N.cursor () Try:          sql = "Update account set money=money-%s where acctid= '%s '"% (money, Acctid) print ( "Reduce_money:" + sql) cursor.execute (SQL) if cursor.rowcount! = 1:raise Exception (" Account%s Reduced payment failed "% Acctid" Finally:cursor.close () def add_money (self, Acctid, money): cursor = self. Conn.cursor () Try:sql = "Update account set money=money+%s where acctid= '%s '"% (money                , Acctid) Print ("Add_money:" + sql) cursor.execute (SQL) if Cursor.rowcount! = 1:  Raise Exception ("account%s bonus failed"% Acctid) finally:cursor.close () def transfer (self, source_acctid, Target_acctid, Money): try:self.check_acct_available (Source_acctid) Self.check_acct_availab Le (Target_acctid) Self.has_enough_money (Source_acctid, Money) Self.reduce_money (Source_acctid, Money ) Self.add_moNey (Target_acctid, Money) self.conn.commit () except Exception as E:self.conn.rollback () Print ("Transfer exception:" + str (e)) raise Edef Main (): Source_acctid = sys.argv[1] Print ("ext. account =" + Sou Rce_acctid) Target_acctid = sys.argv[2] Print ("Transfer to account =" + target_acctid) money = sys.argv[3] Print ("amount =" + ) # Connect to database MySql #conn = Pymysql.     Connect (# host= ' localhost ', # port=3306, # user= ' Root ', # passwd= ' Root ', # db= ' Otkdb ', # charset= ' UTF8 ')

# Connect Database PostgreSQLconn = Psycopg2.connect (host= ' localhost ',port=5432,user= ' Postgres ',password= ' Postgres ',database= ' otkdb ')

    Tr_money = TransferMoney (conn)    try:        tr_money.transfer (Source_acctid, Target_acctid, money)    except Exception as E:        print ("main appears exception:" + str (e))    finally:        conn.close () if __name__ = = ' __main__ ':    Main ( )

Four, the Operation effect

PS h:\web\python> & Python h:\web\Python\01.MySql\db.py 1 2 50 ext account = 1 Transfer account = 2 Amount =50check_acct_available:select * From account where acctid= ' 1 ' check_acct_available:select * from account where acctid= ' 2 ' has_enough_money:select * from AC Count where acctid= ' 1 ' and money>50reduce_money:update account set money=money-50 where acctid= ' 1 ' add_money:update Account set money=money+50 where acctid= ' 2 '

PS h:\web\python> & Python h:\web\Python\01.MySql\db.py 1 2 50 ext account = 1 Transfer account = 2 Amount =50check_acct_available:select * From account where acctid= ' 1 ' check_acct_available:select * from account where acctid= ' 2 ' has_enough_money:select * from AC Count where acctid= ' 1 ' and Money>50transfer exception occurred: Account 1 does not have enough money for the exception: Account 1 does not have enough money

Reference:

Https://www.cnblogs.com/Erick-L/p/7106816.html

Python Learning notes-use of PostgreSQL