通过 pyscopg2 将字符串插入 postgresql [英] Inserting strings into postgresql through pyscopg2

问题描述

我正在尝试将一些字符串值插入到 postgresql 数据库中.即:

<前>MAC地址，时间戳(日期时间格式)设备类型(字符串)子类型(字符串)

示例:

INSERT INTO 设备(地址、tstamp、类型、子类型)值 ('00:00:00:00','2012-02-22 19:31:26','计算机','笔记本')

我正在使用 python psycopg2 脚本来转储数据并且出现以下错误:

c.executemany("INSERT INTO device VALUES (default, '%s','%s','%s','%s')",(mac, date, typedev, subtype,))类型错误:并非所有参数都在字符串格式化期间转换

我用来插入的命令是:

c.executemany("INSERT INTO device VALUES (default, '%s','%s','%s','%s')",(mac, date, typedev, subtype,))

解决方案

有几个问题:

1. 您不需要引用参数占位符 (%s).psycopg2 的优秀员工将确保为您妥善处理.
2. executemany() 期望参数序列.

因此，由于您只插入一行，您可以使用 execute():

c.execute("INSERT INTO device VALUES (default,%s,%s,%s,%s)",(mac, date, typedev, subtype,))

<小时>

当你需要使用 executemany() 时记得给它传递一个参数序列，例如:

c.executemany("INSERT INTO device VALUES (default,%s,%s,%s,%s)",[(mac, date, typedev, subtype,),])

I'm trying to insert some string values into a postgresql db. namely:

macaddress,
timestamp (date time format)
type of device (string)
subtype (string)

example:

INSERT INTO device (address, tstamp, type, subtype) 
     VALUES ('00:00:00:00','2012-02-22 19:31:26','computer','notebook')

I'm using python psycopg2 script to dump the data and the following error is appearing:

c.executemany("INSERT INTO device VALUES (default, '%s','%s','%s','%s')", 
              (mac, date, typedev, subtype,))
TypeError: not all arguments converted during string formatting

the command I'm using to insert is:

c.executemany("INSERT INTO device VALUES (default, '%s','%s','%s','%s')", 
              (mac, date, typedev, subtype,))

解决方案

There are a couple of problems:

1. You don't need to quote the parameter placeholders (%s). The good people of psycopg2 will ensure that is done properly for you.
2. executemany() expects a sequence of parameters.

So since you're only inserting one row you could just use execute():

c.execute("INSERT INTO device VALUES (default,%s,%s,%s,%s)", 
          (mac, date, typedev, subtype,))

---

When you need to use executemany() remember to pass it a sequence of parameters, e.g.:

c.executemany("INSERT INTO device VALUES (default,%s,%s,%s,%s)", 
              [(mac, date, typedev, subtype,),])

这篇关于通过 pyscopg2 将字符串插入 postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！