给定了两个名为students(Name,Lesson(和professors(ProfID,Name,Lessons(的csv文件。
使用给定的代码:
cur.execute('INSERT INTO TABLE (col1, col2, col3) VALUES(%s, %s)', (value1, value2, value3))
cur.execute(query)
set1 = cur.fetchall()
set2 = cur.fetchone()
现有表格:ProfID,名称,课程
没有现有的StudentID。如何将它们分配给每个学生?
如何将上面示例中的列(csv文件中的所有值,不限于上面的3个值(插入到我的表中?以及如何对表的数据进行重复数据消除?
可能有很多可能的解决方案,但我尝试仅使用SQL脚本自行创建
我使用以下脚本创建了一个表
CREATE TABLE public.my_table
(
column_one character varying(20) COLLATE pg_catalog."default",
column_two character varying(20) COLLATE pg_catalog."default",
column_three character varying(20) COLLATE pg_catalog."default",
column_four character varying(20) COLLATE pg_catalog."default"
);
暂时我手动插入数据
INSERT INTO public.my_table(column_one, column_two, column_three, column_four) VALUES ('data1', 'data2', 'data3', 'data4');
INSERT INTO public.my_table(column_one, column_two, column_three, column_four) VALUES ('data11', 'data11', 'data11', 'data11');
INSERT INTO public.my_table(column_one, column_two, column_three, column_four) VALUES ('data21', 'data21', 'data21', 'data21');
INSERT INTO public.my_table(column_one, column_two, column_three, column_four) VALUES ('data31', 'data31', 'data31', 'data31');
INSERT INTO public.my_table(column_one, column_two, column_three, column_four) VALUES ('data41', 'data41', 'data41', 'data41');
INSERT INTO public.my_table(column_one, column_two, column_three, column_four) VALUES ('data51', 'data51', 'data51', 'data51');
INSERT INTO public.my_table(column_one, column_two, column_three, column_four) VALUES ('data61', 'data61', 'data61', 'data61');
然后我按照脚本修改了表格
ALTER TABLE my_table ADD COLUMN column_id INTEGER;
之后,我在循环中更新了列
BEGIN TRANSACTION;
DO $$
DECLARE
col_id bigint := 1 ;
colTemp character(40);
BEGIN
FOR colTemp IN
SELECT * FROM public.my_table
LOOP
UPDATE my_table set column_id = col_id where column_one = colTemp;
col_id := col_id + 1 ;
END LOOP;
END $$;
--ROLLBACK TRANSACTION; --rollback in case of exception
COMMIT TRANSACTION; --commit
更改现有表结构时定义主键
ALTER TABLE my_table ADD PRIMARY KEY (column_id);
注意:在某些测试数据上使用之前,请先对其进行测试。如果我误解了你的问题/要求,请原谅我。我花了很长时间才找到解决办法。任何形式的建议都将不胜感激
编辑描述:我只是想通过一些增强功能来简化问题:-
ALTER TABLE my_table ADD COLUMN column_id INTEGER;
循环中的更改
BEGIN TRANSACTION;
DO $$
DECLARE
col_id bigint := 1 ;
colTemp record ;
BEGIN
FOR colTemp IN
SELECT column_one, column_two, column_three, column_four
FROM my_table
LOOP
UPDATE my_table set column_id = col_id where column_one = colTemp.column_one;
col_id := col_id + 1 ;
END LOOP;
END $$;
--ROLLBACK TRANSACTION; --rollback in case of exception
COMMIT TRANSACTION; --commit
从列中选择最大id并在seq中递增一
SELECT MAX(column_id) FROM my_table;
添加约束
-- select max id and add one to it in start value
CREATE SEQUENCE my_table_colid_seq
START 18
INCREMENT 1
OWNED BY my_table.column_id;
ALTER TABLE my_table ALTER COLUMN column_id SET DEFAULT nextval('my_table_colid_seq');
ALTER TABLE my_table ADD CONSTRAINT column_id_pk PRIMARY KEY (column_id)
解决方案2:
ALTER TABLE my_table ADD COLUMN column_id INTEGER;
CREATE SEQUENCE my_table_colid_seq
START 18
INCREMENT 1
OWNED BY my_table.column_id;
ALTER TABLE my_table ALTER COLUMN column_id SET DEFAULT nextval('my_table_colid_seq');
UPDATE
my_table
SET
column_id = nextval('my_table_colid_seq');
注意-在随机运行此查询之前,只需在测试中运行它,并对数据和表进行实验。请原谅拼写错误。如有任何建议,我们将不胜感激。