Skip navigation links

MySQL Forums :: Connector/Python :: INSERT with SELECT


Advanced Search

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 Philippe Idlas 04/29/2013 01:44AM
Re: INSERT with SELECT Rick James 04/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.