Python之Postgresql数据库操作

Python之Postgresql数据库操作

download: https://www.enterprisedb.com/download-postgresql-binaries

create database:pgAdmin

install postgresql package for python: pip installpsycopg2

install python package: pip installsqlalchemy

create the database connection:

conn = create_engine('postgresql://postgres@127.0.0.1:5432/dbname')

prepare the query:

ls_sql = 'SELECT * FROM tab_name'

read data to a pandas DataFrame from a SQL query:

df_t = pd.read_sql(ls_sql, connp)

write a pandas DataFrame to the database:

df_t.to_sql(s_tab, connp, if_exists='append', index=False)

Update and retrival commands:

import psycopg2

conn = psycopg2.connect(database="dbname", user="username", password="pwd", host="127.0.0.1", port="5432")

cur = conn.cursor()

cur.execute('CREATE TABLE tab_c (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL')

conn.commit()

cur.execute("INSERT INTO tab_c (ID,NAME) VALUES (1, 'a'");

cur.execute("INSERT INTO tab_c (ID,NAME) VALUES (2, 'b'");

conn.commit()

cur.execute("SELECT id, name from tab_c")

rows = cur.fetchall()

for row in rows:

print("ID = ", row[0], "NAME = ", row[1])

conn.close()