pythonアクションpostgreSQL
10049 ワード
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time : 2020/4/21 23:09
# @Author : xhzheng
# @Email : [email protected]
# @File : TestPostgreSQL.py
# @Software: PyCharm
import psycopg2
def createTable(conn,createTableSQL):
cur = conn.cursor()
try:
cur.execute(createTableSQL)
conn.commit()
except:
print("Table created error")
conn.rollback()
else:
print("Table created successfully")
def insertOne(conn,insertOneSQL):
cur = conn.cursor()
try:
cur.execute(insertOneSQL)
conn.commit()
except:
print("Insert one error")
conn.rollback()
else:
print("Insert one successfully")
def selectAll(conn,selectAllSQL):
cur = conn.cursor()
try:
cur.execute(selectAllSQL)
rows = cur.fetchall()
except:
print("Select All error")
else:
print("Select All successfully")
return rows
conn = psycopg2.connect(database="postgres", user="postgres", password="password", host="127.0.0.1", port="5432")
print("Opened database successfully")
#
createTableSQL = '''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);'''
createTable(conn,createTableSQL)
#
insertOneSQL = '''insert into company values (4, 'jj', 15, 'china', 20000.00 );'''
insertOne(conn,insertOneSQL)
#
selectTableSQL = '''select * from company'''
rows = selectAll(conn,selectTableSQL)
for row in rows:
print("ID = ", row[0])
print("NAME = ", row[1])
print("ADDRESS = ", row[2])
print("SALARY = ", row[3], "
")
#
insertOneSQL = '''insert into company values (4, %s, %s, %s, 20000.00 );'''
cur = conn.cursor()
cur.execute(insertOneSQL,('jj',34,'china'))
conn.commit()
conn.close()