SQLAlchemy, Psycopg2 и Postgresql COPY

Ответ 1

Он не похож на него.

Возможно, вам придется просто использовать psycopg2 для раскрытия этой функции и отказаться от возможностей ORM. Я предполагаю, что я действительно не вижу преимущества ORM в такой операции в любом случае, так как это простая вставка вставки и работа с отдельными объектами, а также ORM на самом деле не имеет большого смысла.

Ответ 2

принятый ответ верен, но если вы хотите больше, чем просто комментарий EoghanM, чтобы продолжить, следующее работало для меня в COPYing table out to CSV...

from sqlalchemy import sessionmaker, create_engine

eng = create_engine("postgresql://user:[email protected]:5432/db")
ses = sessionmaker(bind=engine)

dbcopy_f = open('/tmp/some_table_copy.csv','wb')

copy_sql = 'COPY some_table TO STDOUT WITH CSV HEADER'

fake_conn = eng.raw_connection()
fake_cur = fake_conn.cursor()
fake_cur.copy_expert(copy_sql, dbcopy_f)

sessionmaker не требуется, но если вы привыкли одновременно создавать движок и сеанс, чтобы использовать raw_connection, вам нужно будет их разделить (если не существует способа получить доступ к движок через объект сеанса, который я не знаю). Строка sql, предоставленная copy_expert, также не является единственным способом для нее, существует базовая функция copy_to, которую вы можете использовать с подмножеством параметров, которые вы могли бы пройти, до обычного запроса COPY TO. Общая производительность команды кажется мне быстрой, скопировав таблицу из 20000 строк.

http://initd.org/psycopg/docs/cursor.html#cursor.copy_to http://docs.sqlalchemy.org/en/latest/core/connections.html#sqlalchemy.engine.Engine.raw_connection

Ответ 3

Если ваш движок настроен с помощью строки подключения psycopg2 (которая по умолчанию, так что либо "postgresql://..." или "postgresql+psycopg2://..."), вы можете создать курсор psycopg2 из сеанса SQL Alchemy, используя

cursor = session.connection().connection.cursor()

который вы можете использовать для выполнения

cursor.copy_from(...)

Курсор будет активен в той же транзакции, что и ваш сеанс. Если происходит a commit или rollback, любое дальнейшее использование курсора с throw a psycopg2.InterfaceError, вам нужно будет создать новый.

Ответ 4

Вам не нужно опускаться до psycopg2, использовать raw_connection и курсор.

Просто выполните sql, как обычно, вы можете даже использовать параметры привязки с text():

engine.execute(text('''copy some_table from :csv
                       delimiter ',' csv'''
                   ).execution_options(autocommit=True),
               csv='/tmp/a.csv')

Вы можете отказаться от execution_options(autocommit=True), если этот PR будет принят

Ответ 5

Вы можете использовать:

def to_sql(engine, df, table, if_exists='fail', sep='\t', encoding='utf8'):
    # Create Table
    df[:0].to_sql(table, engine, if_exists=if_exists)

    # Prepare data
    output = cStringIO.StringIO()
    df.to_csv(output, sep=sep, header=False, encoding=encoding)
    output.seek(0)

    # Insert data
    connection = engine.raw_connection()
    cursor = connection.cursor()
    cursor.copy_from(output, table, sep=sep, null='')
    connection.commit()
    cursor.close()

Я вставляю 200000 строк за 5 секунд вместо 4 минут

Ответ 6

Если вы можете добраться до двигателя, у вас есть все, что вам нужно сделать:

engine = create_engine('postgresql+psycopg2://myuser:[email protected]/mydb')
# or 
engine = session.engine
# or any other way you know to get to the engine

Теперь вы можете работать.

# isolate a connection
connection = engine.connect().connection

# get the cursor
cursor = connection.cursor()

Вот несколько шаблонов для оператора COPY для использования с cursor.copy_expert(), более полным и гибким вариантом, чем copy_from() или copy_to(), как указано здесь: http://initd.org/psycopg/docs/cursor.html#cursor.copy_expert.

# to dump to a file
dump_to = """
COPY mytable 
TO STDOUT
WITH (
    FORMAT CSV,
    DELIMITER ',',
    HEADER
);
"""

# to copy from a file:
copy_from = """
COPY mytable 
FROM STDIN
WITH (
    FORMAT CSV,
    DELIMITER ',',
    HEADER
);
"""

Посмотрите, что означает вышеперечисленные варианты и другие, которые могут представлять интерес для вашей конкретной ситуации. https://www.postgresql.org/docs/current/static/sql-copy.html.

ВАЖНОЕ ПРИМЕЧАНИЕ. Ссылка на документацию cursor.copy_expert() указывает на использование STDOUT для записи в файл и STDIN для копирования из файла. Но если вы посмотрите на синтаксис руководства PostgreSQL, вы заметите, что вы также можете указать файл для записи непосредственно или непосредственно из инструкции COPY. Не делайте этого, вы, вероятно, просто тратите свое время, если вы не используете root (кто запускает Python как root во время разработки?) Просто выполните то, что указано в документах psycopg2, и укажите STDIN или STDOUT в вашем заявлении с помощью cursor.copy_expert(), это должно быть хорошо.

# running the copy statement
with open('/path/to/your/data/file.csv') as f:
     cursor.copy_expert(copy_from, file=f)

# don't forget to commit the changes.
connection.commit()