PDA

View Full Version : SQLite Woes



morraine
12th August 2008, 17:52
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()



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_fie ld)))
query.addBindValue(QVariant(QString(topic)))
query.exec_()
QApplication.processEvents()


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
12th August 2008, 18:53
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
12th August 2008, 19:22
thanks for your reply jacek

please tell me

what is the difference between using


random_new_field VARCHAR(80) NOT NULL DEFAULT,

and


random_new_field VARCHAR(80),

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

thanks again

jacek
12th August 2008, 20:05
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
12th August 2008, 20:19
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
12th August 2008, 20:27
works like a dream now, thanks