使用Python向SQLite数据库插入多种类型

Question

我正在尝试用Python创建一个SQLite 3数据库。我有几种数据类型想要插入到每条记录中：一个浮点数，然后是3组浮点数，目前这些数据是以元组的形式存在，但也可以是数组或列表。我对Python的了解还不够，无法理解这些之间的区别。我的问题在于INSERT语句。

DAS = 12345
lats = (42,43,44,45)
lons = (10,11,12,13)
times = (1,2,3,4,5,6,7,8,9)

import sqlite3
connection = sqlite3.connect("test.db")
cursor = connection.cursor()
cursor.execute( "create table foo(DAS LONG PRIMARY KEY,lats real(4),lons real(4), times real(9) )" )

我不太确定接下来该怎么写。大概是这样的：

cmd = 'INSERT into foo values (?,?,?,?), ..."
cursor.execute(cmd)

根据这些数据，我应该如何构建SQL插入命令呢？

Answer 1

（一个月后），有两个步骤：
1. 把比如DAS的纬度、经度和时间这些数据压缩成一个很长的列表，比如说变成18个长的项
2. 生成一个类似“插入到表格名称 xx (?,?,... 18个问号)”的语句，然后执行这个语句。

Test = 1

def flatten( *args ):
    """ 1, (2,3), [4,5] -> [1 2 3 4 5] """
    # 1 level only -- SO [python] [flatten] zzz
    all = []
    for a in args:
        all.extend( a if hasattr( a, "__iter__" )  else [a] )
     if Test:  print "test flatten:", all
    return all

def sqlinsert( db, tablename, *args ):
    flatargs = flatten( *args )  # one long list
    ncol = len(flatargs)
    qmarks = "?" + (ncol-1) * ",?"
    insert = "Insert into tablename %s values (%s)" % (tablename, qmarks)
    if Test:  print "test sqlinsert:", insert
    if db:
        db.execute( insert, flatargs )
        # db.executemany( insert, map( flatargs, rows ))
    return insert

#...............................................................................
if __name__ == "__main__":
    print sqlinsert( None, "Table", "hidiho", (4,5), [6] )

Answer 2

像这样：

db = sqlite3.connect("test.db")
cursor = db.cursor()
cursor.execute("insert into foo values (?,?,?,?)", (val1, val2, val3, val4))
db.commit() # Autocommit is off by default (and rightfully so)!

请注意，我并没有使用字符串格式化来把实际数据放进查询里，而是让库自己来处理这个工作。这样数据就会被正确地加上引号和转义。

补充说明：显然，根据你的数据库结构，这样做是行不通的。在一个sqlite数据库的单个字段中存储集合类型的值是不实际的。如果我理解得没错，你应该为每一个要存储的值创建一个单独的列。虽然这样会有很多列，但这是最自然的做法。

Answer 3

类型 real(4) 并不是说有一个包含4个实数的数组、列表或元组；这里的4是用来改变“实数”这个类型的。不过，SQLite大部分情况下会忽略列的类型，因为它采用的是一种叫做“显式类型”的方式，但这些类型仍然会影响列的亲和性。

你有几种选择，比如存储文本表示（通过repr获取）或者使用四个列，每个列存一个值。

你可以利用Python的SQLite库提供的各种钩子来帮你处理一些转换，但如果你需要在SQL中对每个值进行搜索等操作，使用单独的列（并且写一些函数来处理本地化和各种语句，避免重复代码）可能是最简单的方法。

如果你选择存储文本表示，ast.literal_eval（或者在特定条件下使用eval）可以将其转换回Python对象。