SQLite Woes

[morraine]

Hello ive been trying to work out all day long why my sql lite database was not accepting insert queries until i finally found the problem:

basically using this pyqt code:

query.exec_("""CREATE TABLE calls (
                id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                caller VARCHAR(40) NOT NULL,
                starttime DATETIME NOT NULL,
                endtime DATETIME NOT NULL,
                random_new_field VARCHAR(80) NOT NULL,
                topic VARCHAR(80) NOT NULL)""")
 
    topics = ("Complaint", "Information request", "Off topic",
              "Information supplied", "Complaint", "Complaint")
 
    now = QDateTime.currentDateTime()
    print "Populating table..."
    query.prepare("INSERT INTO calls (caller, starttime, endtime, "
                  "topic) VALUES (?, ?, ?, ?)")
    for name in ('Joshan Cockerall', 'Ammanie Ingham',
            'Diarmuid Bettington', 'Juliana Bannister',
            'Oakley-Jay Buxton', 'Reilley Collinge',
            'Ellis-James Mcgehee', 'Jazmin Lawton',
            'Lily-Grace Smythe', 'Coskun Lant', 'Lauran Lanham',
            'Millar Poindexter', 'Naqeeb Neild', 'Maxlee Stoddart',
            'Rebia Luscombe', 'Briana Christine', 'Charli Pease',
            'Deena Mais', 'Havia Huffman', 'Ethan Davie',
            'Thomas-Jack Silver', 'Harpret Bray', 'Leigh-Ann Goodliff',
            'Seoras Bayes', 'Jenna Underhill', 'Veena Helps',
            'Mahad Mcintosh', 'Allie Hazlehurst', 'Aoife Warrington',
            'Cameron Burton', 'Yildirim Ahlberg', 'Alissa Clayton',
            'Josephine Weber', 'Fiore Govan', 'Howard Ragsdale',
            'Tiernan Larkins', 'Seren Sweeny', 'Arisha Keys',
            'Kiki Wearing', 'Kyran Ponsonby', 'Diannon Pepper',
            'Mari Foston', 'Sunil Manson', 'Donald Wykes',
            'Rosie Higham', 'Karmin Raines', 'Tayyibah Leathem',
            'Kara-jay Knoll', 'Shail Dalgleish', 'Jaimie Sells'):
        start = now.addDays(-random.randint(1, 30))
        start = now.addSecs(-random.randint(60 * 5, 60 * 60 * 2))
        end = start.addSecs(random.randint(20, 60 * 13))
        topic = random.choice(topics)
        query.addBindValue(QVariant(QString(name)))
        query.addBindValue(QVariant(start))
        query.addBindValue(QVariant(end))
        query.addBindValue(QVariant(QString(topic)))
        query.exec_()
    QApplication.processEvents()

query.exec_("""CREATE TABLE calls (
                id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
                caller VARCHAR(40) NOT NULL,
                starttime DATETIME NOT NULL,
                endtime DATETIME NOT NULL,
                random_new_field VARCHAR(80) NOT NULL,
                topic VARCHAR(80) NOT NULL)""")
    
    topics = ("Complaint", "Information request", "Off topic",
              "Information supplied", "Complaint", "Complaint")

    now = QDateTime.currentDateTime()
    print "Populating table..."
    query.prepare("INSERT INTO calls (caller, starttime, endtime, "
                  "topic) VALUES (?, ?, ?, ?)")
    for name in ('Joshan Cockerall', 'Ammanie Ingham',
            'Diarmuid Bettington', 'Juliana Bannister',
            'Oakley-Jay Buxton', 'Reilley Collinge',
            'Ellis-James Mcgehee', 'Jazmin Lawton',
            'Lily-Grace Smythe', 'Coskun Lant', 'Lauran Lanham',
            'Millar Poindexter', 'Naqeeb Neild', 'Maxlee Stoddart',
            'Rebia Luscombe', 'Briana Christine', 'Charli Pease',
            'Deena Mais', 'Havia Huffman', 'Ethan Davie',
            'Thomas-Jack Silver', 'Harpret Bray', 'Leigh-Ann Goodliff',
            'Seoras Bayes', 'Jenna Underhill', 'Veena Helps',
            'Mahad Mcintosh', 'Allie Hazlehurst', 'Aoife Warrington',
            'Cameron Burton', 'Yildirim Ahlberg', 'Alissa Clayton',
            'Josephine Weber', 'Fiore Govan', 'Howard Ragsdale',
            'Tiernan Larkins', 'Seren Sweeny', 'Arisha Keys',
            'Kiki Wearing', 'Kyran Ponsonby', 'Diannon Pepper',
            'Mari Foston', 'Sunil Manson', 'Donald Wykes',
            'Rosie Higham', 'Karmin Raines', 'Tayyibah Leathem',
            'Kara-jay Knoll', 'Shail Dalgleish', 'Jaimie Sells'):
        start = now.addDays(-random.randint(1, 30))
        start = now.addSecs(-random.randint(60 * 5, 60 * 60 * 2))
        end = start.addSecs(random.randint(20, 60 * 13))
        topic = random.choice(topics)
        query.addBindValue(QVariant(QString(name)))
        query.addBindValue(QVariant(start))
        query.addBindValue(QVariant(end))
        query.addBindValue(QVariant(QString(topic)))
        query.exec_()
    QApplication.processEvents()

To copy to clipboard, switch view to plain text mode 

it creates the database table but does not insert the information because there are more fields in the database than there is in the insert query?!?!?!?

Now i come from using php and mysql background and it no problem at all to insert less data into a database than the fields that take up the database.

IS this normal behaviour? im i the one who is crazy?

basically to make this query work i had to put the extra field in the query and just send a null value to is in the addBindValue variable for it:

query.prepare("INSERT INTO calls (caller, starttime, endtime, random_new_field, "
                  "topic) VALUES (?, ?, ?, ?, ?)")
    for name in ('Joshan Cockerall', 'Ammanie Ingham',
            'Diarmuid Bettington', 'Juliana Bannister',
            'Oakley-Jay Buxton', 'Reilley Collinge',
            'Ellis-James Mcgehee', 'Jazmin Lawton',
            'Lily-Grace Smythe', 'Coskun Lant', 'Lauran Lanham',
            'Millar Poindexter', 'Naqeeb Neild', 'Maxlee Stoddart',
            'Rebia Luscombe', 'Briana Christine', 'Charli Pease',
            'Deena Mais', 'Havia Huffman', 'Ethan Davie',
            'Thomas-Jack Silver', 'Harpret Bray', 'Leigh-Ann Goodliff',
            'Seoras Bayes', 'Jenna Underhill', 'Veena Helps',
            'Mahad Mcintosh', 'Allie Hazlehurst', 'Aoife Warrington',
            'Cameron Burton', 'Yildirim Ahlberg', 'Alissa Clayton',
            'Josephine Weber', 'Fiore Govan', 'Howard Ragsdale',
            'Tiernan Larkins', 'Seren Sweeny', 'Arisha Keys',
            'Kiki Wearing', 'Kyran Ponsonby', 'Diannon Pepper',
            'Mari Foston', 'Sunil Manson', 'Donald Wykes',
            'Rosie Higham', 'Karmin Raines', 'Tayyibah Leathem',
            'Kara-jay Knoll', 'Shail Dalgleish', 'Jaimie Sells'):
        start = now.addDays(-random.randint(1, 30))
        start = now.addSecs(-random.randint(60 * 5, 60 * 60 * 2))
        end = start.addSecs(random.randint(20, 60 * 13))
        topic = random.choice(topics)
        random_new_field = ""
        query.addBindValue(QVariant(QString(name)))
        query.addBindValue(QVariant(start))
        query.addBindValue(QVariant(end))
        query.addBindValue(QVariant(QString(random_new_field)))
        query.addBindValue(QVariant(QString(topic)))
        query.exec_()
    QApplication.processEvents()

query.prepare("INSERT INTO calls (caller, starttime, endtime, random_new_field, "
                  "topic) VALUES (?, ?, ?, ?, ?)")
    for name in ('Joshan Cockerall', 'Ammanie Ingham',
            'Diarmuid Bettington', 'Juliana Bannister',
            'Oakley-Jay Buxton', 'Reilley Collinge',
            'Ellis-James Mcgehee', 'Jazmin Lawton',
            'Lily-Grace Smythe', 'Coskun Lant', 'Lauran Lanham',
            'Millar Poindexter', 'Naqeeb Neild', 'Maxlee Stoddart',
            'Rebia Luscombe', 'Briana Christine', 'Charli Pease',
            'Deena Mais', 'Havia Huffman', 'Ethan Davie',
            'Thomas-Jack Silver', 'Harpret Bray', 'Leigh-Ann Goodliff',
            'Seoras Bayes', 'Jenna Underhill', 'Veena Helps',
            'Mahad Mcintosh', 'Allie Hazlehurst', 'Aoife Warrington',
            'Cameron Burton', 'Yildirim Ahlberg', 'Alissa Clayton',
            'Josephine Weber', 'Fiore Govan', 'Howard Ragsdale',
            'Tiernan Larkins', 'Seren Sweeny', 'Arisha Keys',
            'Kiki Wearing', 'Kyran Ponsonby', 'Diannon Pepper',
            'Mari Foston', 'Sunil Manson', 'Donald Wykes',
            'Rosie Higham', 'Karmin Raines', 'Tayyibah Leathem',
            'Kara-jay Knoll', 'Shail Dalgleish', 'Jaimie Sells'):
        start = now.addDays(-random.randint(1, 30))
        start = now.addSecs(-random.randint(60 * 5, 60 * 60 * 2))
        end = start.addSecs(random.randint(20, 60 * 13))
        topic = random.choice(topics)
        random_new_field = ""
        query.addBindValue(QVariant(QString(name)))
        query.addBindValue(QVariant(start))
        query.addBindValue(QVariant(end))
        query.addBindValue(QVariant(QString(random_new_field)))
        query.addBindValue(QVariant(QString(topic)))
        query.exec_()
    QApplication.processEvents()

To copy to clipboard, switch view to plain text mode 

the main reson i ask about this is is this just an sqlite problem or is this a qt problem and i will have this same issue what ever database i put on the end of these queries.

or have i totally f-d up the way i should be using the sql module and i can actually just insert one new field and row into a table with 20 colums if ishould ever feel like so with out having to send null values to the rest of the colums

[jacek]

random_new_field VARCHAR(80) NOT NULL,

This field was declared with "NOT NULL" constraint, which means that you have to give it some value.

query.prepare("INSERT INTO calls (caller, starttime, endtime, "
"topic) VALUES (?, ?, ?, ?)")

Here you don't specify that random_new_field. This means it will be set to the default value, which is null and as you saw above it is forbidden.

Now i come from using php and mysql background and it no problem at all to insert less data into a database than the fields that take up the database.

Except for "NOT NULL" fields.

IS this normal behaviour? im i the one who is crazy?

Yes (at least for the former ).

basically to make this query work i had to put the extra field in the query and just send a null value to is in the addBindValue variable for it:
...
random_new_field = ""
...
query.addBindValue(QVariant(QString(random_new_fie ld)))

If you look closely you will see that you don't set it to NULL, but to an empty string. You can avoid the problem by adding DEFAULT '' or removing NOT NULL constraint.

[morraine]

thanks for your reply jacek

please tell me

what is the difference between using

random_new_field VARCHAR(80) NOT NULL DEFAULT,

random_new_field VARCHAR(80) NOT NULL DEFAULT,

To copy to clipboard, switch view to plain text mode 

and

random_new_field VARCHAR(80),

random_new_field VARCHAR(80),

To copy to clipboard, switch view to plain text mode 

which is better to use or what would be the right way to use them both?

thanks again

[jacek]

random_new_field VARCHAR(80) NOT NULL DEFAULT,

You have to specify some value after DEFAULT. For example DEFAULT ''.

The difference is that if you keep NOT NULL, that field never will be null (but it still can be an empty string).

which is better to use or what would be the right way to use them both?

You can use both (i.e. declare a field that has a default value and is nullable at the same time), but null values can cause trouble, so it is better to avoid them. Unless you have some good reason for using nulls in random_new_field, you should use the first option (NOT NULL DEFAULT '').

[morraine]

Sorry yes i forgot to put the single quote marks of a nothing string

Thanks for you help on this mate your a life saver

[morraine]

works like a dream now, thanks