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()