MagicStack / asyncpg

A fast PostgreSQL Database Client Library for Python/asyncio.
Apache License 2.0
7.02k stars 403 forks source link

greenplum->copy_records_to_table support needed #333

Open HuangKaibo2017 opened 6 years ago

HuangKaibo2017 commented 6 years ago

try to run copy_records_to_table against greenplum 5.7 which report error as: File "D:\Anaconda3\envs\DataLightFire37\lib\site-packages\asyncpg\connection.py", line 848, in _copy_in_records copy_stmt, None, None, records, intro_stmt, timeout) File "asyncpg\protocol\protocol.pyx", line 504, in copy_in asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "("

Try to run those against PostgreSQL 9.2. It passed. Also, execute and executemany work fine with Greenplum.

ljluestc commented 10 months ago
import asyncpg

async def copy_records_to_table(connection, table_name, records):
    try:
        # Check if it's Greenplum
        is_greenplum = await connection.fetchval("SELECT version() LIKE '%%Greenplum%%'")

        # Use different copy statement syntax based on the database
        if is_greenplum:
            copy_stmt = f"COPY {table_name} FROM stdin WITH CSV"
        else:
            copy_stmt = f"COPY {table_name} FROM stdin WITH CSV DELIMITER ','"

        # Start the copy operation
        await connection.execute(copy_stmt)

        # Prepare and send the data
        for record in records:
            await connection.copy_records_to_table(table_name, records=[record])

        # Complete the copy operation
        await connection.copy_records_done()

    except Exception as e:
        print(f"Error during copy: {e}")
        raise

async def main():
    conn = await asyncpg.connect(
        user='your_user',
        password='your_password',
        database='your_database',
        host='your_host'
    )

    table_name = 'your_table'
    records = [
        (1, 'John'),
        (2, 'Jane'),
        # Add more records here
    ]

    await copy_records_to_table(conn, table_name, records)

if __name__ == '__main__':
    import asyncio
    asyncio.run(main())