如何使用 SQLite 批量插入?

2022-01-23 00:00:00 bulkinsert sqlite ado.net

如何使用 SQLite 批量插入?

How do I bulk insert with SQLite?

我查了一下，好像我用 select 语句进行了插入.我搜索了一下，查看了示例，它们看起来都像是在将数据从一个表复制到另一个表，或者与 SQLite 不兼容.我想做类似的事情

I looked it up and it seems like I do an insert with a select statement. I googled, looked at the examples and they all look like they are copying data from one table to another or is not compatible with SQLite. I want to do something like

"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 error no such column: watcher"

I tried the code below and I get the error "SQLite error no such column: watcher"

        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, mediaId, catagory, 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 绑定支持的命名占位符样式)这应该可以工作:

SQlite doesn't support @variable notation, but (using named-placeholder style as supported by the Python binding of sqlite for clarity) this should work:

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

SQLite 似乎误诊了哪个列名是错误的.列名全部固定后，以下 Python 代码适用于我(不确定您使用的是哪种其他语言，Python 是我与 sqlite 交互最方便的语言):

SQLite seems to be misdiagnosing what column name is wrong. With column names all fixed, the following Python code works for me (not sure what other language you're using, Python's what handiest to me to interact with 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, " +
            "recipientId  INTEGER, " +
            "mediaId      INTEGER, " +
            "catagory     INTEGER, " +
            "current_date DATE)")
cur.execute("CREATE TABLE if not exists user_watch( " +
            "indx INTEGER PRIMARY KEY, " +
            "watcher INTEGER, " +
            "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, mediaId, catagory, current_date) " +
        "SELECT watcher, :mediaId, :category, :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，我可以让它误诊为缺少的列是 watcher，即使该列实际上没问题.想尝试将这个更正后的代码放回您最喜欢的语言中，看看它的表现如何?

But if I reproduce mismatches in your SQL such as current_date vs current_media_date, I can get it to mis-diagnose that the missing column is watcher, even though that column is in fact fine. Want to try putting this corrected code back into your favorite language and see how it behaves?

相关文章