Support for psycopg3

[isratmir]

Is there a plan to add support for https://www.psycopg.org/psycopg3/docs/index.html?

[palewire]

I can't say I've thought about it before. Do you know what kind of changes that might require?

[DeeeeLAN]

The most notable change I faced was the removal of copy_expert. Here is a psql_insert_copy method for uploading a pandas dataframe to the database which I updated for psycopg3. Hopefully it helps.

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

def psql_insert_copy(table, conn, keys, data_iter):
	"""
	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([f'"{k}"' for k in keys])
		if table.schema:
			table_name = f'{table.schema}.{table.name}'
		else:
			table_name = table.name

		sql = f'COPY {table_name} ({columns}) FROM STDIN WITH CSV'

		with cur.copy(sql) as copy:
			while data := s_buf.read(10240):
				copy.write(data)