如何使用 psycopg2 读取和插入 bytea 列? [英] How to read and insert bytea columns using psycopg2?

问题描述

我正在编写一个 Python 脚本来将一些 Postgresql 表从一个环境复制到另一个环境(比 pg_dump 做的多一点).除非我复制具有 bytea 数据类型的表，否则它可以工作.

I am working on a Python script to replicate some Postgresql tables from one environment to another (which does a little more than pg_dump). It works except when I am copying a table that has bytea data type.

我读取内存中的源表数据，然后使用串联插入转储目标数据库中的内存.

I read the source table data in memory, then I dump the memory in the target database with concatenated inserts.

这是我生成插入语句的方法:

Here is my method that produces an insert statement:

def generateInsert(self, argCachedRow):

    colOrd = 0;

    valClauseList = []

    hasBinary = False

    for colData in argCachedRow:
        colOrd += 1
        colName = self.colOrdLookup.get(colOrd)
        col = self.colLookup.get(colName)
        dataType = col.dataType

        insVal = None

        if colData is not None:

            strVal = str(colData)
            if dataType.useQuote:

                if "'" in strVal:
                    strVal = strVal.replace("'", "''")
                insVal = "'%s'" % strVal
            else:
                if dataType.binary:
                    hasBinary = True
                    #insVal = psycopg2.Binary(colData)
                #else:

                insVal = strVal
        else:
            insVal = "NULL"

        valClauseList.append(insVal)

    valClause = ", ".join(valClauseList)

    if hasBinary:
        valClause = psycopg2.Binary(valClause)

    result = "INSERT INTO %s VALUES (%s)" % (self.name, valClause)

    return result

适用于每个没有二进制数据的表.

which works with every table that doesn't have binary data.

我还尝试(直观地)将二进制列数据包装在 psycopg2.Binary 中，这是注释掉的行，然后不对整个行值列表执行此操作，但这并没有工作.

I also tried (intuitively) to wrap just the binary column data in psycopg2.Binary, which is the commented out line and then not do it to the whole row value list but that didn't work either.

这是我的简单 DataType 包装器，它是通过读取 Postgres 的 information_schema 表加载的:

Here is my simple DataType wrapper, which is loaded by reading Postgres' information_schema tables:

class DataType(object):
    def __init__(self, argDispName, argSqlName, argUseQuote, argBin):
        self.dispName = argDispName
        self.sqlName = argSqlName
        self.useQuote = argUseQuote
        self.binary = argBin

如何使用 psycopg2 读取和插入 bytea 列?

How do I read and insert bytea columns using psycopg2?

推荐答案

如果你有这个数据库结构:

If you have this database structure:

CREATE TABLE test (a bytea,
                   b int,
                   c text)

然后可以像这样将二进制数据插入请求中，无需任何包装器:

then inserting binary data into the request can be done like so, without any wrappers:

bin_data = b'bytes object'
db = psycopg2.connect(*args)  # DB-API 2.0
c = db.cursor()
c.execute('''INSERT INTO test VALUES (%s, %s, %s)''', (bin_data, 1337, 'foo'))
c.execute('''UPDATE test SET a = %s''', (bin_data + b'1',))

然后，当你查询它时:

c.execute('''SELECT a FROM test''')

您将收到memoryview，很容易转换回bytes:

mview = c.fetchone()
new_bin_data = bytes(mview)
print(new_bin_data)

输出:b'bytes object1'

另外，我建议您不要通过字符串格式组合查询.psycopg2 的内置参数替换更方便，您不必担心验证数据以防止 SQL 注入.

Also, I'd suggest you not to assemble queries by string formatting. psycopg2's built-in parameter substitution is much more convenient and you don't have to worry about validating data to protect from SQL injections.

这篇关于如何使用 psycopg2 读取和插入 bytea 列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！