473,237 Members | 1,395 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 software developers and data experts.

INSERT statements not INSERTING when using mysql from python

Ben
I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.

I first connect to the database...

self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")

All the neccesary tables are created...

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)

self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))

Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:

print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"

I have, for debugging, set "i" up as a counter variable.

No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)

I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)

It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.

Cheers,

Ben

Dec 29 '06 #1
14 2863
Ben
Well that's odd...

If I place the exact same Insert statement elswhere in the program it
works as intended.
That would suggest it is never being run in its old position, but the
statements either side of it are printing...
Ben wrote:
I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.

I first connect to the database...

self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")

All the neccesary tables are created...

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)

self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))

Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:

print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"

I have, for debugging, set "i" up as a counter variable.

No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)

I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)

It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.

Cheers,

Ben
Dec 29 '06 #2
Ben wrote:
I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.

I first connect to the database...

self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")

All the neccesary tables are created...

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)

self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))

Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:

print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"

I have, for debugging, set "i" up as a counter variable.

No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)

I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)

It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.

Cheers,

Ben
Not sure this will help but where is the "commit"? I don't use MySQL but
most SQL engines require a commit.
Johnf
Dec 29 '06 #3
Ben

Ben wrote:
Well that's odd...

If I place the exact same Insert statement elswhere in the program it
works as intended.
That would suggest it is never being run in its old position, but the
statements either side of it are printing...
Ben wrote:
I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.

I first connect to the database...

self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")

All the neccesary tables are created...

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)

self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))

Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:

print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"

I have, for debugging, set "i" up as a counter variable.

No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)

I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)

It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.

Cheers,

Ben
Well, it would appear to be some kind of autocommit problem. I had
autocommit off, bus committed when I disconnected from the database.
For some reason although all the other statements seemed ok, multiple
statements in that loop were'nt every commiting. For the moment I've
turned autocommit on and that has sorted things out, but it slows
things down too so I'll try to fix it :-)

Ben

Dec 29 '06 #4
Ben
I initially had it set up so that when I connected to the database I
started a transaction, then when I disconnected I commited.

I then tried turning autocommit on, but that didn't seem to make any
difference (althouh initially I thought it had)

I'll go back and see what I can find...
Cheers,
Ben
johnf wrote:
Ben wrote:
I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.

I first connect to the database...

self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")

All the neccesary tables are created...

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)

self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))

Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:

print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"

I have, for debugging, set "i" up as a counter variable.

No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)

I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)

It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.

Cheers,

Ben

Not sure this will help but where is the "commit"? I don't use MySQL but
most SQL engines require a commit.
Johnf
Dec 29 '06 #5
Ben
One partial explanation might be that for some reason it is recreating
the table each time the code runs. My code says "CREATE TABLE IF NOT
EXISTS" but if for some reason it is creating it anyway and dropping
the one before that could explain why there are missing entires.

It wouldn't explain why the NOT EXISTS line is being ignored though...

Ben
Ben wrote:
I initially had it set up so that when I connected to the database I
started a transaction, then when I disconnected I commited.

I then tried turning autocommit on, but that didn't seem to make any
difference (althouh initially I thought it had)

I'll go back and see what I can find...
Cheers,
Ben
johnf wrote:
Ben wrote:
I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.
>
I first connect to the database...
>
self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")
>
All the neccesary tables are created...
>
self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)
>
self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))
>
Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:
>
print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"
>
I have, for debugging, set "i" up as a counter variable.
>
No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)
>
I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)
>
It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.
>
Cheers,
>
Ben
Not sure this will help but where is the "commit"? I don't use MySQL but
most SQL engines require a commit.
Johnf
Dec 29 '06 #6
Ben
Each time my script is run, the following is called:

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)
self.cursor.execute("CREATE TABLE IF NOT EXISTS table_name ( ....

The idea being that stuf is only created the first time the script is
run, and after that the original tables and database is used. This
might explain my pronblem if for some reason the old tables are being
replaced... can anyone see anything wrong with the above?

Ben


Ben wrote:
One partial explanation might be that for some reason it is recreating
the table each time the code runs. My code says "CREATE TABLE IF NOT
EXISTS" but if for some reason it is creating it anyway and dropping
the one before that could explain why there are missing entires.

It wouldn't explain why the NOT EXISTS line is being ignored though...

Ben
Ben wrote:
I initially had it set up so that when I connected to the database I
started a transaction, then when I disconnected I commited.

I then tried turning autocommit on, but that didn't seem to make any
difference (althouh initially I thought it had)

I'll go back and see what I can find...
Cheers,
Ben
johnf wrote:
Ben wrote:
>
I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.

I first connect to the database...

self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")

All the neccesary tables are created...

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)

self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))

Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:

print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"

I have, for debugging, set "i" up as a counter variable.

No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)

I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)

It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.

Cheers,

Ben
>
Not sure this will help but where is the "commit"? I don't use MySQL but
most SQL engines require a commit.
Johnf
Dec 29 '06 #7
Ben
Nope... that can't be it. I tried running those commands manually and
nothing went wrong.
But then again when I execute the problematic command manually nothing
goes wrong. Its just not executing until the last time, or being
overwritten.
Ben wrote:
Each time my script is run, the following is called:

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)
self.cursor.execute("CREATE TABLE IF NOT EXISTS table_name ( ....

The idea being that stuf is only created the first time the script is
run, and after that the original tables and database is used. This
might explain my pronblem if for some reason the old tables are being
replaced... can anyone see anything wrong with the above?

Ben


Ben wrote:
One partial explanation might be that for some reason it is recreating
the table each time the code runs. My code says "CREATE TABLE IF NOT
EXISTS" but if for some reason it is creating it anyway and dropping
the one before that could explain why there are missing entires.

It wouldn't explain why the NOT EXISTS line is being ignored though...

Ben
Ben wrote:
I initially had it set up so that when I connected to the database I
started a transaction, then when I disconnected I commited.
>
I then tried turning autocommit on, but that didn't seem to make any
difference (althouh initially I thought it had)
>
I'll go back and see what I can find...
Cheers,
Ben
>
>
johnf wrote:
Ben wrote:

I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.
>
I first connect to the database...
>
self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")
>
All the neccesary tables are created...
>
self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)
>
self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))
>
Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:
>
print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"
>
I have, for debugging, set "i" up as a counter variable.
>
No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)
>
I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)
>
It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.
>
Cheers,
>
Ben

Not sure this will help but where is the "commit"? I don't use MySQL but
most SQL engines require a commit.
Johnf
Dec 29 '06 #8
Ben
Well, I've checked the SQL log, and my insert statements are certainly
being logged. The only option left open is that the table in question
is being replaced, but I can't see why it should be...
Ben wrote:
Nope... that can't be it. I tried running those commands manually and
nothing went wrong.
But then again when I execute the problematic command manually nothing
goes wrong. Its just not executing until the last time, or being
overwritten.
Ben wrote:
Each time my script is run, the following is called:

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)
self.cursor.execute("CREATE TABLE IF NOT EXISTS table_name ( ....

The idea being that stuf is only created the first time the script is
run, and after that the original tables and database is used. This
might explain my pronblem if for some reason the old tables are being
replaced... can anyone see anything wrong with the above?

Ben


Ben wrote:
One partial explanation might be that for some reason it is recreating
the table each time the code runs. My code says "CREATE TABLE IF NOT
EXISTS" but if for some reason it is creating it anyway and dropping
the one before that could explain why there are missing entires.
>
It wouldn't explain why the NOT EXISTS line is being ignored though...
>
Ben
>
>
Ben wrote:
I initially had it set up so that when I connected to the database I
started a transaction, then when I disconnected I commited.

I then tried turning autocommit on, but that didn't seem to make any
difference (althouh initially I thought it had)

I'll go back and see what I can find...
Cheers,
Ben


johnf wrote:
Ben wrote:
>
I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.

I first connect to the database...

self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")

All the neccesary tables are created...

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)

self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))

Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:

print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"

I have, for debugging, set "i" up as a counter variable.

No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)

I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)

It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.

Cheers,

Ben
>
Not sure this will help but where is the "commit"? I don't use MySQL but
most SQL engines require a commit.
Johnf
Dec 29 '06 #9
Ben
I have found the problem, but not the cause.

I tried setting the database up manually before hand, which let me get
rid of the "IF NOT EXISTS" lines, and now it works!

But why the *** should it not work anyway? The first time it is run, no
database or tables, so it creates them. That works. But apparentlyu on
subsequent runs it decides the tables it created arent' actually there,
and overwrites them. Grrrrrrrrrrrrr.
Ben

Ben wrote:
Well, I've checked the SQL log, and my insert statements are certainly
being logged. The only option left open is that the table in question
is being replaced, but I can't see why it should be...
Ben wrote:
Nope... that can't be it. I tried running those commands manually and
nothing went wrong.
But then again when I execute the problematic command manually nothing
goes wrong. Its just not executing until the last time, or being
overwritten.
Ben wrote:
Each time my script is run, the following is called:
>
self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)
self.cursor.execute("CREATE TABLE IF NOT EXISTS table_name ( ....
>
The idea being that stuf is only created the first time the script is
run, and after that the original tables and database is used. This
might explain my pronblem if for some reason the old tables are being
replaced... can anyone see anything wrong with the above?
>
Ben
>
>
>
>
>
>
Ben wrote:
One partial explanation might be that for some reason it is recreating
the table each time the code runs. My code says "CREATE TABLE IF NOT
EXISTS" but if for some reason it is creating it anyway and dropping
the one before that could explain why there are missing entires.

It wouldn't explain why the NOT EXISTS line is being ignored though...

Ben


Ben wrote:
I initially had it set up so that when I connected to the database I
started a transaction, then when I disconnected I commited.
>
I then tried turning autocommit on, but that didn't seem to make any
difference (althouh initially I thought it had)
>
I'll go back and see what I can find...
Cheers,
Ben
>
>
johnf wrote:
Ben wrote:

I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.
>
I first connect to the database...
>
self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")
>
All the neccesary tables are created...
>
self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)
>
self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))
>
Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:
>
print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"
>
I have, for debugging, set "i" up as a counter variable.
>
No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)
>
I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)
>
It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.
>
Cheers,
>
Ben

Not sure this will help but where is the "commit"? I don't use MySQL but
most SQL engines require a commit.
Johnf
Dec 29 '06 #10
Ben
Perhaps when I'm checking for table existance using mysql through
python I have to be more explicit:

Where I did have:
USE database;
IF NOT EXISTS CREATE table(.....

Perhaps I need:
USE database;
IF NOT EXISTS CREATE database.table(.....

I'll try it after lunch. Does anyoone know whether this might be the
problem?

Ben
Ben wrote:
I have found the problem, but not the cause.

I tried setting the database up manually before hand, which let me get
rid of the "IF NOT EXISTS" lines, and now it works!

But why the *** should it not work anyway? The first time it is run, no
database or tables, so it creates them. That works. But apparentlyu on
subsequent runs it decides the tables it created arent' actually there,
and overwrites them. Grrrrrrrrrrrrr.
Ben

Ben wrote:
Well, I've checked the SQL log, and my insert statements are certainly
being logged. The only option left open is that the table in question
is being replaced, but I can't see why it should be...
Ben wrote:
Nope... that can't be it. I tried running those commands manually and
nothing went wrong.
But then again when I execute the problematic command manually nothing
goes wrong. Its just not executing until the last time, or being
overwritten.
>
>
Ben wrote:
Each time my script is run, the following is called:

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)
self.cursor.execute("CREATE TABLE IF NOT EXISTS table_name ( ....

The idea being that stuf is only created the first time the script is
run, and after that the original tables and database is used. This
might explain my pronblem if for some reason the old tables are being
replaced... can anyone see anything wrong with the above?

Ben






Ben wrote:
One partial explanation might be that for some reason it is recreating
the table each time the code runs. My code says "CREATE TABLE IF NOT
EXISTS" but if for some reason it is creating it anyway and dropping
the one before that could explain why there are missing entires.
>
It wouldn't explain why the NOT EXISTS line is being ignored though...
>
Ben
>
>
Ben wrote:
I initially had it set up so that when I connected to the database I
started a transaction, then when I disconnected I commited.

I then tried turning autocommit on, but that didn't seem to make any
difference (althouh initially I thought it had)

I'll go back and see what I can find...
Cheers,
Ben


johnf wrote:
Ben wrote:
>
I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.

I first connect to the database...

self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")

All the neccesary tables are created...

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)

self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))

Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:

print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"

I have, for debugging, set "i" up as a counter variable.

No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)

I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)

It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.

Cheers,

Ben
>
Not sure this will help but where is the "commit"? I don't use MySQL but
most SQL engines require a commit.
Johnf
Dec 29 '06 #11
Not sure if this will help, as you said you already tried autocommit,
but did you try to commit after creating the table, then doing all the
inserts before commiting on disconnect?

(I'm no MySQL or Python guru, but I do use it with python and MySQLdb .)

Dec 29 '06 #12
Ask Ben, he might know, although he's out to lunch.

Ben wrote:
I'll try it after lunch. Does anyoone know whether this might be the
problem?

Ben
Ben wrote:
I have found the problem, but not the cause.

I tried setting the database up manually before hand, which let me get
rid of the "IF NOT EXISTS" lines, and now it works!

But why the *** should it not work anyway? The first time it is run, no
database or tables, so it creates them. That works. But apparentlyu on
subsequent runs it decides the tables it created arent' actually there,
and overwrites them. Grrrrrrrrrrrrr.
Ben

Ben wrote:
Well, I've checked the SQL log, and my insert statements are certainly
being logged. The only option left open is that the table in question
is being replaced, but I can't see why it should be...
>
>
Ben wrote:
Nope... that can't be it. I tried running those commands manually and
nothing went wrong.
But then again when I execute the problematic command manually nothing
goes wrong. Its just not executing until the last time, or being
overwritten.


Ben wrote:
Each time my script is run, the following is called:
>
self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)
self.cursor.execute("CREATE TABLE IF NOT EXISTS table_name ( ....
>
The idea being that stuf is only created the first time the script is
run, and after that the original tables and database is used. This
might explain my pronblem if for some reason the old tables are being
replaced... can anyone see anything wrong with the above?
>
Ben
>
>
>
>
>
>
Ben wrote:
One partial explanation might be that for some reason it is recreating
the table each time the code runs. My code says "CREATE TABLE IF NOT
EXISTS" but if for some reason it is creating it anyway and dropping
the one before that could explain why there are missing entires.

It wouldn't explain why the NOT EXISTS line is being ignored though...

Ben


Ben wrote:
I initially had it set up so that when I connected to the database I
started a transaction, then when I disconnected I commited.
>
I then tried turning autocommit on, but that didn't seem to make any
difference (althouh initially I thought it had)
>
I'll go back and see what I can find...
Cheers,
Ben
>
>
johnf wrote:
Ben wrote:

I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.
>
I first connect to the database...
>
self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")
>
All the neccesary tables are created...
>
self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)
>
self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))
>
Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:
>
print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"
>
I have, for debugging, set "i" up as a counter variable.
>
No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)
>
I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)
>
It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.
>
Cheers,
>
Ben

Not sure this will help but where is the "commit"? I don't use MySQL but
most SQL engines require a commit.
Johnf
Dec 29 '06 #13
Ben
:-) Ok, point taken!

I fixed it in the end. It was nothing interesting at all - just a
wayward line of code that was doing exactly what I feared - replacing
the database each time rather than just when it was needed.

Ben
Leo Kislov wrote:
Ask Ben, he might know, although he's out to lunch.

Ben wrote:
I'll try it after lunch. Does anyoone know whether this might be the
problem?

Ben
Ben wrote:
I have found the problem, but not the cause.
>
I tried setting the database up manually before hand, which let me get
rid of the "IF NOT EXISTS" lines, and now it works!
>
But why the *** should it not work anyway? The first time it is run, no
database or tables, so it creates them. That works. But apparentlyu on
subsequent runs it decides the tables it created arent' actually there,
and overwrites them. Grrrrrrrrrrrrr.
>
>
Ben
>
>
>
Ben wrote:
Well, I've checked the SQL log, and my insert statements are certainly
being logged. The only option left open is that the table in question
is being replaced, but I can't see why it should be...


Ben wrote:
Nope... that can't be it. I tried running those commands manually and
nothing went wrong.
But then again when I execute the problematic command manually nothing
goes wrong. Its just not executing until the last time, or being
overwritten.
>
>
Ben wrote:
Each time my script is run, the following is called:

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)
self.cursor.execute("CREATE TABLE IF NOT EXISTS table_name ( ....

The idea being that stuf is only created the first time the script is
run, and after that the original tables and database is used. This
might explain my pronblem if for some reason the old tables are being
replaced... can anyone see anything wrong with the above?

Ben






Ben wrote:
One partial explanation might be that for some reason it is recreating
the table each time the code runs. My code says "CREATE TABLE IF NOT
EXISTS" but if for some reason it is creating it anyway and dropping
the one before that could explain why there are missing entires.
>
It wouldn't explain why the NOT EXISTS line is being ignored though...
>
Ben
>
>
Ben wrote:
I initially had it set up so that when I connected to the database I
started a transaction, then when I disconnected I commited.

I then tried turning autocommit on, but that didn't seem to make any
difference (althouh initially I thought it had)

I'll go back and see what I can find...
Cheers,
Ben


johnf wrote:
Ben wrote:
>
I don't know whether anyone can help, but I have an odd problem. I have
a PSP (Spyce) script that makes many calls to populate a database. They
all work without any problem except for one statement.

I first connect to the database...

self.con = MySQLdb.connect(user=username, passwd =password)
self.cursor = self.con.cursor()
self.cursor.execute("SET max_error_count=0")

All the neccesary tables are created...

self.cursor.execute("CREATE DATABASE IF NOT EXISTS "+name)
self.cursor.execute("USE "+name)

self.cursor.execute("CREATE TABLE IF NOT EXISTS networks (SM
varchar(20),DMC int,DM varchar(50),NOS int,OS varchar(50),NID
varchar(20))

Then I execute many insert statements in various different loops on
various tables, all of which are fine, and result in multiple table
entries. The following one is executed many times also. and seems
identical to the rest. The print statements output to the browser
window, and appear repeatedly, so the query must be being called
repeatedly also:

print "<p><b>SQL query executing</b><p>"
self.cursor.execute("INSERT INTO networks VALUES ('a',' "+i+"
','c','2','e','f','g')")
print "<p><b>SQL query executed</b><p>"

I have, for debugging, set "i" up as a counter variable.

No errors are given, but the only entry to appear in the final database
is that from the final execution of the INSERT statement (the last
value of i)

I suspect that this is to vague for anyone to be able to help, but if
anyone has any ideas I'd be really grateful :-)

It occured to me that if I could access the mysql query log that might
help, but I was unsure how to enable logging for MysQL with python.

Cheers,

Ben
>
Not sure this will help but where is the "commit"? I don't use MySQL but
most SQL engines require a commit.
Johnf
Dec 29 '06 #14
"Ben" <Be*************@gmail.comwrote:
>Perhaps when I'm checking for table existance using mysql through
python I have to be more explicit:

Where I did have:
USE database;
IF NOT EXISTS CREATE table(.....

Perhaps I need:
USE database;
IF NOT EXISTS CREATE database.table(.....

I'll try it after lunch. Does anyoone know whether this might be the
problem?
The "IF NOT EXISTS" clauses are not standard SQL; they are MySQL
extensions. Although my reasons are nebulous and more related to principle
than to practicality, I try never to use them, because they might not be
available in my next database.

After all, you should have a pretty good idea at any given time whether
your database and table already exist.

I do occasionally allow myself a "DROP TABLE IF NOT EXISTS", which then
allows the unadulterated "CREATE TABLE" command.
--
Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Dec 30 '06 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: Jack | last post by:
I have a text file of data in a file (add2db.txt) where the entries are already entered on separate lines in the following form: INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
3
by: kristof.loots | last post by:
Hi all, Somehow I want to transfer data from an mysql database on the web to an local offline access database. Note: tables and types do not match. So, I toughed, generating sql commands to...
7
by: Kevin Lawrence | last post by:
Hi all I want to do "INSERT INTO Table (Blob) Values('blobdataasstring')". ...rather than using the parameter driven method, is it possible? And if so what encoder do I use to convert the...
9
by: David Eades | last post by:
Hi all Complete newbie here, so apologies if this is the wrong forum. I've been asked to use mysql and asp to make a simple bidding system (rather like a simple ebay), whereby users can use a...
2
by: speralta | last post by:
My tired old eyes may be failing me, but the following insert statements look correct to me, but I can't seem to get a clean insert from a fairly large text file database into mysql. I was...
3
by: Dmitri | last post by:
Hello! I have a developer that is playing around with some SQL statements using VB.NET. He has a test table in a SQL 2000 database, and he has about 2000 generated INSERT statements. When the...
8
by: Red | last post by:
If auto-format is turned off in VS2008, there is apparently no way to indent a line. Under Tools->Options->Text Editor->C#->Formatting, there are three checkboxes. Unchecking those seems to cause...
0
by: brianrpsgt1 | last post by:
I am attempting to insert data from a HTML form using a .psp script. I can not find how to link the data that is inserted into the form to the variables in the .psp script to then insert into the...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.