Setelah sebelumnya membuat database di PostgreSQL, saat ini akan ditampilkan cara:
- Membuat tabel
- Menginput data
- Menampilkan data
- Update data
- Menghapus data
Saat ini dalam contoh kode program ini saya akan menggunakan library psycopg2 merupakan library yang secara standar terinstall saat kita install python.
Membuat File koneksi ke database
"""
Created on Wed Apr 10 17:03:24 2019
@author: Wawan H Nur
Koneksi ke database di POstgreSQL
"""
import psycopg2
import Conf_postgresql as psql
PGHOST = "localhost"
PGDATABASE = "py_geo_db"
PGUSER = "userwhn"
PGPASSWORD = "pwdwhn"
PGSCHEMA = "geodata"
conn_string = "host="+ psql.PGHOST+ " port=" + "5432" + " dbname="+ psql.PGDATABASE + " user=" + psql.PGUSER+" password="+ psql.PGPASSWORD
conn = psycopg2.connect(conn_string)
print("Connection Success")
Membuat Tabel di Database
"""
Created on Wed Apr 10 17:08:57 2019
@author: Wawan HN
Membuat tabel di database py_geo_db dengan schema=geodata
"""
import psycopg2
import Connection_to_DB as con
connect = con.conn
cur = connect.cursor()
cur.execute('''CREATE TABLE '''+con.PGSCHEMA+'''.PERSON2 (ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADRESS CHAR(50),
SALARY REAL);''')
print("Table created successfuly")
connect.commit()
connect.close()
Menginput data ke tabel di Database
"""
Created on Wed Apr 10 17:44:26 2019
@author: Wawan H Nur
Menambahkan data dalam tabel
"""
import psycopg2
import Connection_to_DB as con
connect = con.conn
cur = connect.cursor()
cur.execute("INSERT INTO " +con.PGSCHEMA+".COMPANY (ID, NAME, AGE, ADRESS, SALARY)\
VALUES(3,'Bruce', '32', 'Ujungberung', 20000.00)");
cur.execute("INSERT INTO " +con.PGSCHEMA+".COMPANY (ID, NAME, AGE, ADRESS, SALARY)\
VALUES(4,'Tony', '33', 'Bojongsoang', 30000.00)");
cur.execute("INSERT INTO " +con.PGSCHEMA+".COMPANY (ID, NAME, AGE, ADRESS, SALARY)\
VALUES(5,'Dede', '32', 'Texas', 20000.00)");
cur.execute("INSERT INTO " +con.PGSCHEMA+".COMPANY (ID, NAME, AGE, ADRESS, SALARY)\
VALUES(6,'Johny', '33', 'Cicaheum', 50000.00)");
connect.commit()
print("Records created succesfully")
connect.close()
Menampilkan data dari Tabel
"""
Created on Wed Apr 10 17:57:48 2019
@author: Wawan H Nur
Menampilkan data
"""
import psycopg2
import Connection_to_DB as con
connect = con.conn
cur = connect.cursor()
cur.execute("SELECT * FROM "+ con.PGSCHEMA +".company")
rows = cur.fetchall()
for row in rows:
print("ID = ", row[0])
print("NAME = ", row[1])
print("AGE = ", row[2])
print("ADDRESS = ", row[3])
print("SALARY = ", row[4],"\n")
print("Operation done successfully")
connect.close()
Update data dalam suatu Tabel
"""
Created on Wed Apr 10 18:28:07 2019
@author: Wawan H Nur
Update data
"""
import psycopg2
import Connection_to_DB as con
connect = con.conn
cur = connect.cursor()
cur.execute("UPDATE " +con.PGSCHEMA+".COMPANY set SALARY = 25000.00 WHERE ID=2")
connect.commit()
print("Total number of rows updated: ", cur.rowcount)
cur.execute("SELECT * FROM "+ con.PGSCHEMA +".company")
rows = cur.fetchall()
for row in rows:
print("ID = ", row[0])
print("NAME = ", row[1])
print("AGE = ", row[2])
print("ADDRESS = ", row[3])
print("SALARY = ", row[4],"\n")
print("Operation done successfully")
connect.close()
Menghapus data dari Tabel
"""
Created on Wed Apr 10 18:37:17 2019
@author: Wawan H Nur
Delete data
"""
import psycopg2
import Connection_to_DB as con
connect = con.conn
cur = connect.cursor()
cur.execute("DELETE FROM " +con.PGSCHEMA+ ".company WHERE ID=2;")
connect.commit()
print("Total number of rows deleted: ", cur.rowcount)
cur.execute("SELECT * FROM "+ con.PGSCHEMA +".company")
rows = cur.fetchall()
for row in rows:
print("ID = ", row[0])
print("NAME = ", row[1])
print("AGE = ", row[2])
print("ADDRESS = ", row[3])
print("SALARY = ", row[4],"\n")
print("Operation done successfully")
connect.close()
Demikian dulu tulisan ini, semoga bermanfaat. Silahkan tunggu untuk tulisan selanjunya @wawanhn
Refrensi: https://www.tutorialspoint.com/postgresql/postgresql_python.htm
Menginput data ke tabel di Database
"""
Created on Wed Apr 10 17:44:26 2019
@author: Wawan H Nur
Menambahkan data dalam tabel
"""
import psycopg2
import Connection_to_DB as con
connect = con.conn
cur = connect.cursor()
cur.execute("INSERT INTO " +con.PGSCHEMA+".COMPANY (ID, NAME, AGE, ADRESS, SALARY)\
VALUES(3,'Bruce', '32', 'Ujungberung', 20000.00)");
cur.execute("INSERT INTO " +con.PGSCHEMA+".COMPANY (ID, NAME, AGE, ADRESS, SALARY)\
VALUES(4,'Tony', '33', 'Bojongsoang', 30000.00)");
cur.execute("INSERT INTO " +con.PGSCHEMA+".COMPANY (ID, NAME, AGE, ADRESS, SALARY)\
VALUES(5,'Dede', '32', 'Texas', 20000.00)");
cur.execute("INSERT INTO " +con.PGSCHEMA+".COMPANY (ID, NAME, AGE, ADRESS, SALARY)\
VALUES(6,'Johny', '33', 'Cicaheum', 50000.00)");
connect.commit()
print("Records created succesfully")
connect.close()
Menampilkan data dari Tabel
"""
Created on Wed Apr 10 17:57:48 2019
@author: Wawan H Nur
Menampilkan data
"""
import psycopg2
import Connection_to_DB as con
connect = con.conn
cur = connect.cursor()
cur.execute("SELECT * FROM "+ con.PGSCHEMA +".company")
rows = cur.fetchall()
for row in rows:
print("ID = ", row[0])
print("NAME = ", row[1])
print("AGE = ", row[2])
print("ADDRESS = ", row[3])
print("SALARY = ", row[4],"\n")
print("Operation done successfully")
connect.close()
Update data dalam suatu Tabel
"""
Created on Wed Apr 10 18:28:07 2019
@author: Wawan H Nur
Update data
"""
import psycopg2
import Connection_to_DB as con
connect = con.conn
cur = connect.cursor()
cur.execute("UPDATE " +con.PGSCHEMA+".COMPANY set SALARY = 25000.00 WHERE ID=2")
connect.commit()
print("Total number of rows updated: ", cur.rowcount)
cur.execute("SELECT * FROM "+ con.PGSCHEMA +".company")
rows = cur.fetchall()
for row in rows:
print("ID = ", row[0])
print("NAME = ", row[1])
print("AGE = ", row[2])
print("ADDRESS = ", row[3])
print("SALARY = ", row[4],"\n")
print("Operation done successfully")
connect.close()
Menghapus data dari Tabel
"""
Created on Wed Apr 10 18:37:17 2019
@author: Wawan H Nur
Delete data
"""
import psycopg2
import Connection_to_DB as con
connect = con.conn
cur = connect.cursor()
cur.execute("DELETE FROM " +con.PGSCHEMA+ ".company WHERE ID=2;")
connect.commit()
print("Total number of rows deleted: ", cur.rowcount)
cur.execute("SELECT * FROM "+ con.PGSCHEMA +".company")
rows = cur.fetchall()
for row in rows:
print("ID = ", row[0])
print("NAME = ", row[1])
print("AGE = ", row[2])
print("ADDRESS = ", row[3])
print("SALARY = ", row[4],"\n")
print("Operation done successfully")
connect.close()
Demikian dulu tulisan ini, semoga bermanfaat. Silahkan tunggu untuk tulisan selanjunya @wawanhn
Refrensi: https://www.tutorialspoint.com/postgresql/postgresql_python.htm
