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

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181216B0GFPR00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券