MySQL Forums
Forum List  »  Connector/Python

INSERT with SELECT
Posted by: Philippe Idlas
Date: April 29, 2013 01:44AM

Hi,

I run a python program which takes a flat log file as input to load it into a database in order for administrators to monitor their application.

I have five tables : server table with two columns (id and name), status table with two columns, directive table with two columns, rule table with two columns and finally a server_status_directive_rule table with five columns (id_server, id_status, id_directive, id_rule, date).

I can easily insert into the first four tables.
My problem is with trying to insert a variable into the fifth table.
The code is :
<code>
query = """INSERT INTO serveur_etat_directive_regle (id_serveur, id_etat, id_directive, id_regle, date)
SELECT id_serveur, id_etat, id_directive, id_regle, %s FROM serveur, etat, directive, regle
WHERE nom_serveur = %s AND nom_etat = %s AND nom_directive = %s AND nom_regle = %s"""
cursor.execute(query, (serveur, etat, directive, regle, date,))
</code>

The problem is with the date column. If I remove it from my INSERT and subsequently in the SELECT and cursor.execute, the table is filled in properly.

The other thing to know is I have no error message returned although I do manage error messages. My python code runs with no messages and my table is still empty at the end.

The complete SQL code is below :
<code>
try:
# accès et mise à jour de la BDD
db = mdb.connect('localhost', 'root', '', 'rudder');
cursor = db.cursor()
query = """INSERT IGNORE INTO serveur (nom_serveur) VALUES (%s)"""
cursor.execute(query, (serveur,))
query = """INSERT IGNORE INTO etat (nom_etat) VALUES (%s)"""
cursor.execute(query, (etat,))
query = """INSERT IGNORE INTO directive (nom_directive) VALUES (%s)"""
cursor.execute(query, (directive,))
query = """INSERT IGNORE INTO regle (nom_regle) VALUES (%s)"""
cursor.execute(query, (regle,))
query = """INSERT INTO serveur_etat_directive_regle (id_serveur, id_etat, id_directive, id_regle, date)
SELECT id_serveur, id_etat, id_directive, id_regle, %s FROM serveur, etat, directive, regle
WHERE nom_serveur = %s AND nom_etat = %s AND nom_directive = %s AND nom_regle = %s"""
cursor.execute(query, (serveur, etat, directive, regle, date,))
except mdb.Error, e:
print "Error %d: %s" % (e.args[0],e.args[1])
sys.exit(1)
db.commit()
</code>

Any help would be much appreciated.

KR.

Options: ReplyQuote


Subject
Written By
Posted
INSERT with SELECT
April 29, 2013 01:44AM
April 30, 2013 08:22PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.