创建新的 SQLite 表，将其他表中的列与 sqlite3 和 python 组合起来 [英] Create new SQLite table combining column from other tables with sqlite3 and python

问题描述

我正在尝试创建一个新表，该表将来自两个不同表的列组合在一起.让我们想象一下，我有一个名为 db.db 的数据库，其中包含两个名为 table1 和 table2 的表.

I am trying to create a new table that combines columns from two different tables. Let's imagine then that I have a database named db.db that includes two tables named table1 and table2.

table1 看起来像这样:

table1 looks like this:

id | item | price
-------------
 1 | book | 20  
 2 | copy | 30   
 3 | pen  | 10 

和 table2 像这样(注意有重复的轴):

and table2 like this (note that has duplicated axis):

id | item | color
-------------
 1 | book | blue  
 2 | copy | red   
 3 | pen  | red 
 1 | book | blue  
 2 | copy | red   
 3 | pen  | red 

现在我正在尝试创建一个名为 new_table 的新表，该表在同一轴上组合了列 price 和 color 并且没有重复项.我的代码如下(由于我的 SQL 技能不佳，它显然不起作用):

Now I'm trying to create a new table named new_table that combines both columns price and color over the same axis and also without duplicates. My code is the following (it does not obviously work because of my poor SQL skills):

con = sqlite3.connect(":memory:")
cur = con.cursor()

cur.execute("CREATE TABLE new_table (id varchar, item integer, price integer, color integer)")
cur.execute("ATTACH DATABASE 'db.db' AS other;")
cur.execute("INSERT INTO new_table (id, item, price) SELECT * FROM other.table1")
cur.execute("UPDATE new_table SET color = (SELECT color FROM other.table2 WHERE distinct(id))")
con.commit()

我知道最后一行代码中有多个错误，但我无法理解.你对这个问题的处理方法是什么?谢谢！

I know there are multiple errors in the last line of code but I can't get my head around it. What would be your approach to this problem? Thanks!

推荐答案

类似的东西

CREATE TABLE new_table(id INTEGER, item TEXT, price INTEGER, color TEXT);
INSERT INTO new_table(id, item, price, color)
  SELECT DISTINCT t1.id, t1.item, t1.price, t2.color
  FROM table1 AS t1
  JOIN table2 AS t2 ON t1.id = t2.id;

注意固定的列类型；你的很奇怪.item 和 color 作为整数?

Note the fixed column types; yours were all sorts of strange. item and color as integers?

如果每个 id 值在新表中都是唯一的(只有一行的 id 为 1，只有 2，依此类推)，该列可能应该是 INTEGER PRIMARY KEY，也是.

If each id value is unique in the new table (Only one row will ever have an id of 1, only will be 2, and so on), that column should probably be an INTEGER PRIMARY KEY, too.

另外，由于您是从基于附加文件的数据库中的表在内存数据库中创建此表......也许您想要一个临时表?或者一个视图可能更合适?不确定您的目标是什么.

Also, since you're creating this table in an in-memory database from tables from an attached file-based database... maybe you want a temporary table instead? Or a view might be more appropriate? Not sure what your goal is.

这篇关于创建新的 SQLite 表，将其他表中的列与 sqlite3 和 python 组合起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！