我如何批量插入使用SQLite？ [英] How do I bulk insert with SQLite?
问题描述
如何大量使用SQLite插入？
我看着它，它好像我做一个select语句插入。我用Google搜索，看了看例子，他们看起来都像是从一个表中的数据复制到另一个或不使用SQLite兼容。我想要做这样的事情
INSERT INTO user_msg_media（recipientId，mediaId，catagory，current_media_date）+
VALUES（@mediaId，@catagory，@current_media_date）;
其中recipientId的值是从每个所述观察者
选择观察家从userwatch WHERE观看= @看了;
我想下面的code和我得到的错误SQLite的错误没有这样的列：守望者
command.CommandText =
CREATE TABLE如果不存在user_msg_media（+
MSGID INTEGER PRIMARY KEY，+
recipientId INTEGER，+
mediaId INTEGER，+
catagory INTEGER，+
CURRENT_DATE日期）;;
command.ExecuteNonQuery（）;
//用户媒体
command.CommandText =
CREATE TABLE如果不存在user_watch（+
INDX INTEGER PRIMARY KEY，+
守望者INTEGER，+
看着整数）;
command.ExecuteNonQuery（）;
// ...
command.CommandText =SELECT观察家从user_watch WHERE观看=：观看;;
command.Parameters.Add（：看了，DbType.Int64）。价值= 1;
command.ExecuteNonQuery（）; //是确定
command.CommandText =
INSERT INTO user_msg_media（recipientId，mediaId，catagory，current_media_date）+
选择守望者，：mediaId，：类别：current_media_date+
从user_watch WHERE观看=：观看;;
command.Parameters.Add（：mediaId，DbType.Int64）。价值= 0;
command.Parameters.Add（：类别，DbType.Int64）。价值= 0;
command.Parameters.Add（：current_media_date，DbType.Int64）。价值= 0;
command.Parameters.Add（：看了，DbType.Int64）。价值= 1;
command.ExecuteNonQuery（）;
SQLite不支持@variable符号，但（使用命名占位符风格由Python SQLite，让清晰的绑定，支持）这应该工作：
INSERT INTO user_msg_media（用户ID，mediaId，catagory，current_media_date）
选择守望者，：mediaId，：类别，：current_media_date
从userwatch WHERE观看=：观看
编辑：SQLite的似乎是什么误诊列名是错误的。列名的所有固定，下面的Python code对我的作品（不知道你用什么其他的语言，Python的最方便的是什么对我使用SQLite交互）：
导入sqlite3的是平
CON = sq.connect（'：内存：'）
CUR = con.cursor（）
cur.execute（CREATE TABLE如果不存在user_msg_media（+
MSGID INTEGER PRIMARY KEY，+
recipientId INTEGER，+
mediaId INTEGER，+
catagory INTEGER，+
CURRENT_DATE DATE））
cur.execute（CREATE TABLE如果不存在user_watch（+
INDX INTEGER PRIMARY KEY，+
守望者INTEGER，+
看着INTEGER））
cur.execute（插入user_watch VALUES（1，2，3））
cur.execute（选择守望者从user_watch WHERE观看=：看了，
字典（看了= 3））
打印cur.fetchall（）
打印cur.execute（INSERT INTO user_msg_media（recipientId，mediaId，catagory，CURRENT_DATE）+
选择守望者，：mediaId，：类别：current_media_date+
FROM user_watch WHERE观看=：观看;，
字典（mediaId = 0，类别= 0，current_media_date = 0，看了= 3）
）
cur.execute（SELECT * FROM user_msg_media）
打印cur.fetchall（）
但是，如果我复制不匹配您的SQL如CURRENT_DATE VS current_media_date，我可以得到它误诊断的缺失列观察家，即使该列事实上罚款。想尝试把这个修正code返回到您喜欢的语言，看看它是如何工作？
How do I bulk insert with 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";
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 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
Edit: 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()
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?
这篇关于我如何批量插入使用SQLite？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
