sqlite empty inserts

[sapsi]

Hello,
I created a table like:

create table __saved_query__ (vdb_name text , query_table_name text
PRIMARY KEY, query text)

and then I insert as follows

self.cursor.execute( "insert into __saved_query__(vdb_name,
query_table_name, query) values (?,?,?)", (vdbname,
foldername,where_query))
self.conn.commit()
Now for some reason, the first insert works, but thereafter all
inserts result in empty rows
e.g
sqlite> select * from __saved_query__;
TestVDB|test_b|title regexp 'boo'
||
||

The last two rows are from the 2nd and 3rd inserts. The values i;m
inserting are non null.

Any ideas?

Thank you for your time
Saptarshi

 

[Peter Otten]

I created a table like:

create table __saved_query__ (vdb_name text , query_table_name text
PRIMARY KEY, query text)

and then I insert as follows

self.cursor.execute( "insert into __saved_query__(vdb_name,
query_table_name, query) values (?,?,?)", (vdbname,
foldername,where_query))
self.conn.commit()
Now for some reason, the first insert works, but thereafter all
inserts result in empty rows
e.g
sqlite> select * from __saved_query__;
TestVDB|test_b|title regexp 'boo'
||
||

The last two rows are from the 2nd and 3rd inserts. The values i;m
inserting are non null.

Any ideas?

Change the query to ... query_table_name text PRIMARY KEY NOT NULL ... to
verify that assertion before you look any further.

Peter

 

[sapsi]

Hi,
Thank you for the tip, you're absolutely right.I;m getting an
exception that the name must not be null. However, this is some debug
output
--- logging.debug(" %s %s %s" % (vdbname, foldername,where_query))
OUTPUT: TestVDB test_b title regexp 'tit'
--- print vdbname.__class__,
foldername.__class__,where_query.__class__
OUTPUT: <type 'str'> <type 'str'> <type 'str'>

--- self.cursor.execute( "insert into __saved_query__(vdb_name,
query_table_name, query) values (?,?,?)", (vdbname,
foldername,where_query))

OUTPUT: __saved_query__.vdb_name may not be NULL

I honestly don't get it!. Why should this occur?
Regards
Saptarshi

 

[sapsi]

I forgot to mention that i was creating a view just before this i.e
sql="create view %s as %s" % (foldername, query)
# self.cursor.execute(sql)
self.cursor.execute(sql)

Now if i remove this, the insert code works fine.
Regards
Saptarshi

 

[Peter Otten]

sapsi wrote:

[please don't top-post]

Thank you for the tip, you're absolutely right.I;m getting an
exception that the name must not be null. However, this is some debug
output
--- logging.debug(" %s %s %s" % (vdbname, foldername,where_query))
OUTPUT: TestVDB test_b title regexp 'tit'
--- print vdbname.__class__,
foldername.__class__,where_query.__class__
OUTPUT: <type 'str'> <type 'str'> <type 'str'>

--- self.cursor.execute( "insert into __saved_query__(vdb_name,
query_table_name, query) values (?,?,?)", (vdbname,
foldername,where_query))

OUTPUT: __saved_query__.vdb_name may not be NULL

I honestly don't get it!. Why should this occur?

Because vbdname is None?

Seriously, it's hard for us to tell what's going on without the relevant
code. If you don't provide enough context you are on your own.

Peter

 

[sapsi]

sapsi wrote:

[please don't top-post]

Thank you for the tip, you're absolutely right.I;m getting an
exception  that the name must not be null. However, this is some debug
output
--- logging.debug(" %s %s %s" %  (vdbname, foldername,where_query))
OUTPUT: TestVDB test_b title regexp 'tit'
--- print  vdbname.__class__,
foldername.__class__,where_query.__class__
OUTPUT: <type 'str'> <type 'str'> <type 'str'>

--- self.cursor.execute( "insert into __saved_query__(vdb_name,
query_table_name, query) values (?,?,?)", (vdbname,
foldername,where_query))

OUTPUT: __saved_query__.vdb_name may not be NULL

I honestly don't get it!. Why should this occur?

Because vbdname is None?

Seriously, it's hard for us to tell what's going on without the relevant
code. If you don't provide enough context you are on your own.

Peter

Actualy, vdbname is not none. I checked the value before entering. I
found out that by closing the connection and reopening after creating
VIEW solved the problem.
Not a nice solution but it works and not a hassle given the low
frequency of the operation.
Regards
Saptarshi

--code
self.cursor.execute( "insert into __smartfolder__ values (?,?,?,?)",
(vdbname, foldername,where_query,hrform))
self.conn.commit()
self.cursor.execute("CREATE VIEW %s AS %s" % (foldername,
query) )
self.conn.close()
self.connect_to_db()