如何使用SQLite批量插入？

如何使用sqlite批量插入？

我查了一下,好像我用select语句插入.我用Google搜索,看看这些示例,他们看起来都像是将数据从一个表复制到另一个表或者与sqlite不兼容.我想做点什么

"INSERT INTO user_msg_media (recipientId,mediaId,catagory,current_media_date) " +
"VALUES(@mediaId,@catagory,@current_media_date)";
where the value of recipientId is the watcher from each of
"SELECT watcher FROM userwatch WHERE watched=@watched";

我尝试了下面的代码,我得到错误“sqlite错误没有这样的列：观察者”

command.CommandText =
            "CREATE TABLE if not exists user_msg_media( " +
            "msgId        INTEGER PRIMARY KEY," +
            "recipientId  INTEGER," +
            "mediaId      INTEGER," +
            "catagory     INTEGER," +
            "current_date DATE);";
        command.ExecuteNonQuery();

        //user media
        command.CommandText =
            "CREATE TABLE if not exists user_watch( " +
            "indx INTEGER PRIMARY KEY," +
            "watcher INTEGER," +
            "watched INTEGER);";
        command.ExecuteNonQuery();
        //...

    command.CommandText = "SELECT watcher FROM user_watch WHERE watched=:watched;";
    command.Parameters.Add(":watched",DbType.Int64).Value = 1;
    command.ExecuteNonQuery(); //is ok

    command.CommandText =
        "INSERT INTO user_msg_media (recipientId,current_media_date) " +
        "SELECT watcher,:mediaId,:category,:current_media_date" +
        "FROM user_watch WHERE watched=:watched;";
    command.Parameters.Add(":mediaId",DbType.Int64).Value = 0;
    command.Parameters.Add(":category",DbType.Int64).Value = 0;
    command.Parameters.Add(":current_media_date",DbType.Int64).Value = 0;
    command.Parameters.Add(":watched",DbType.Int64).Value = 1;
    command.ExecuteNonQuery();

sqlite不支持@variable表示法,但是(为了清楚起见,使用sqlite的Python绑定支持的命名占位符样式)这应该有效：

INSERT INTO user_msg_media (userId,current_media_date)
SELECT watcher,:current_media_date
FROM userwatch WHERE watched=:watched

编辑：sqlite似乎误解了什么列名错了.由于列名全部修复,以下Python代码适用于我(不确定您使用的是其他语言,Python对我来说最方便与sqlite交互)：

import sqlite3 as sq

con = sq.connect(':memory:')
cur = con.cursor()
cur.execute("CREATE TABLE if not exists user_msg_media( " +
            "msgId        INTEGER PRIMARY KEY," +
            "current_date DATE)")
cur.execute("CREATE TABLE if not exists user_watch( " +
            "indx INTEGER PRIMARY KEY," +
            "watched INTEGER)")

cur.execute("INSERT INTO user_watch VALUES (1,2,3)")

cur.execute("SELECT watcher FROM user_watch WHERE watched=:watched",dict(watched=3))
print cur.fetchall()

print cur.execute("INSERT INTO user_msg_media (recipientId,current_date) " +
        "SELECT watcher,:current_media_date " +
        "FROM user_watch WHERE watched=:watched;",dict(mediaId=0,category=0,current_media_date=0,watched=3)
)

cur.execute("SELECT * FROM user_msg_media")
print cur.fetchall()

但是如果我在你的sql中重现不匹配,例如current_date和current_media_date,我可以让它错误地诊断丢失的列是观察者,即使该列实际上很好.想要尝试将此更正后的代码重新用于您喜欢的语言并查看其行为方式吗？

如何使用SQLite批量插入？

猜你在找的Sqlite相关文章