每次我运行python程序时如何添加新列

问题描述

我希望我的表的第1列为滚动号，第2列为名称.每当我运行python程序时，我都希望在表中添加一列日期.并且在该新列中，我要填充从中获取的列表user.list将包含值'P'，'A'，'P'，'P'等.如何处理?我尝试先通过alter命令添加一列，然后插入数据，但是没有任何效果.

i want my table to have 1st column as roll no,second as name.whenever i run python program i want to add a column of date in table.and in that new column i want to populate the list which i get from user.list will contain values 'P','A','P','P' etc.how to go about it?i tried first adding a column by alter command and then inserting data but nothing works.

choice1=raw_input("\nEnter 'y' or 'n' to add new column:\n")
if choice1 is 'y':
    cursor.execute(" ALTER TABLE table1 ADD datecolumn varchar(20)")
    db.commit()
    for i in xrange(0,10):
        if students[i] is 'A':
            cursor.execute("insert into table1(datetime) values ('A')")
            db.commit()
        elif students[i] is 'P':
            cursor.execute("insert  into table1(datetime) values ('P')")
            db.commit()

推荐答案

不要在运行时更改数据库设计，而是以一种可以更改数据而不是结构的方式进行设计.

Don't change your DB design at runtime, but design it in a way that you will change the data not the structure.

您可以有两个表.一个名为Student且具有列rollno和name的列，如果rollno是唯一的，则可能是rollno上的PK.

You could have two tables. One called Student with columns rollno and name, maybe PK on rollno if it is unique.

然后有另一个名为Thing的表(任何合适的名称，但我不知道您的数据是关于什么的)，其中有三列when(日期时间)，value(任何合适的名称)(CHAR(1 ))和student(与rollno相同).

Then have another table called Thing (any suitable name, but I don't know what your data is about) with three columns when (datetime), value (any suitable name) (CHAR(1)) and student (same type as rollno).

在when和value两者上定义PK，以确保每个学生每个日期只有一个值.从Thing.student到Student.rollno定义FK.现在，您的数据库将负责保持您的数据(大部分)保持一致.

Define PK over both when and value to ensure that each student has only one value per date. Define a FK from Thing.student to Student.rollno. Now your DB takes care of keeping your data (mostly) consistent.

根据您对不同列的选择，插入和更新的需求来定义索引.

Define indices depending on your needs of selects, inserts and updates over the different columns.

然后用于查询联接两个表以获得所需的结果，例如

Then for querying join both tables to get the desired result, e.g.

select s.name, t.value
from Student s
left join Thing t on t.student = s.rollno
where t.when == 'whenever'

(我不确定mysql的方言，因此可能需要更多的引号.请随时进行编辑.)

(I am not sure about the mysql dialect, so maybe some more quotes are needed. Please feel free to edit.)

这篇关于每次我运行python程序时如何添加新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！