acytoo

Python 连接 postgresql 数据库

#安装

Python 下有一个连接 postgresql 数据库的包，叫 psycopg，最新版本 3。 安装 psycopg3，需要pip版本 20.3 以上，所以先更新 pip。

pip install --upgrade pip 
pip install psycopg\[binary\]
#基础用法

下面的代码来自 postgresql 官方文档

# Note: the module name is psycopg, not psycopg3
import psycopg

# Connect to an existing database
with psycopg.connect("dbname=test user=postgres") as conn:

    # Open a cursor to perform database operations
    with conn.cursor() as cur:

        # Execute a command: this creates a new table
        cur.execute("""
            CREATE TABLE test (
                id serial PRIMARY KEY,
                num integer,
                data text)
            """)

        # Pass data to fill a query placeholders and let Psycopg perform
        # the correct conversion (no SQL injections!)
        cur.execute(
            "INSERT INTO test (num, data) VALUES (%s, %s)",
            (100, "abc'def"))

        # Query the database and obtain data as Python objects.
        cur.execute("SELECT * FROM test")
        cur.fetchone()
        # will return (1, 100, "abc'def")

        # You can use `cur.fetchmany()`, `cur.fetchall()` to return a list
        # of several records, or even iterate on the cursor
        for record in cur:
            print(record)

        # Make the changes to the database persistent
        conn.commit()

在实际操作中，经常将数据库操作封装起来，作为一个类对外服务

class DBHandler:
  """
  Database handler
  """
  def __init__(self) -> None:
    self._conn = psycopg.connect("dbname=testdb user=someone password=123456")


  def addtagname(self, tid: int, tname: str):
    with self._conn.cursor() as cur:
      query = f"INSERT INTO tag_name_table (tid, tname) VALUES ({tid}, '{tname}')"
      cur.execute(query)
      self._conn.commit()

  def selecttag(self):
    pass

  def __del__(self):
    self._conn.close()

相关文章：