Pandas ENH: Improve typing in SQL Insertion method (improve docs about how to do this)

Feature Type

• [ ] Adding new functionality to pandas

• [X] Changing existing functionality in pandas

• [ ] Removing existing functionality in pandas

Problem Description

i would like to have properly type SQL Insertion method from: https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#io-sql-method

Currently however pyright is showing one error:

  from pandas.io.sql import SQLTable
  from sqlalchemy.engine.base import Connection
  from typing import Iterable

  # Alternative to_sql() *method* for DBs that support COPY FROM
  import csv
  from io import StringIO

  def psql_insert_copy(table: SQLTable, conn:Connection, keys: list[str], data_iter: Iterable) -> None:
      """
      Execute SQL statement inserting data

      Parameters
      ----------
      table : pandas.io.sql.SQLTable
      conn : sqlalchemy.engine.Engine or sqlalchemy.engine.Connection
      keys : list of str
          Column names
      data_iter : Iterable that iterates the values to be inserted
      """
      # gets a DBAPI connection that can provide a cursor
      dbapi_conn = conn.connection
      with dbapi_conn.cursor() as cur:
          s_buf = StringIO()
          writer = csv.writer(s_buf)
          writer.writerows(data_iter)
          s_buf.seek(0)

          columns = ', '.join(['"{}"'.format(k) for k in keys])
          if table.schema:
              table_name = '{}.{}'.format(table.schema, table.name)
          else:
              table_name = table.name

          sql = 'COPY {} ({}) FROM STDIN WITH CSV'.format(
              table_name, columns)

Feature Description

Pandas docs would describe how to do this properly, so no error is shown.

Alternative Solutions

n/a

Additional Context

No response

Comment From: phofl

Hi, thanks for your report. This is an Alchemy object, I can't see how this is related to pandas

Comment From: rhshadrach

Agreed. We're not going to leave functionality out of the documentation because it doesn't pass type-checkers. This would be good to report to Alchemy.