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)

[davidszotten]

i'm also interested in psycopg3 support, mostly in the context of moving the rest of my app to psycopg3. i've started looking and it doesn't seem too bad to support both. would you consider a patch? we might need to add tox or something similar to make it easier to test with both libraries installed separately, does that sound ok?