MySQL Forums
Forum List  »  Connector/Python

TypeError when inserting a date
Posted by: Philippe Idlas
Date: April 25, 2013 03:35AM

Hi,

As far as I know I can insert a date into MySQL as long as the format corresponds to the date field attribute.
My date column in MySQL is declared as datetime.
So if I insert a date like '2013-04-21 11:29:00' into it it should work fine.
Indeed when I insert using phpmysql it works but when I insert from my python programm it fails with the following error message :

Traceback (most recent call last):
File "C:\Users\pidlas\Documents\rudder_find.py", line 58, in <module>
extract(info)
File "C:\Users\pidlas\Documents\rudder_find.py", line 46, in extract
cursor.execute(query, (serveur, etat, directive, regle, date,))
File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 183, in execute
query = query % db.literal(args)
TypeError: not all arguments converted during string formatting

I have tried different ways of formatting my python date variable but I could not until now found the right thing to do.

I would appreciate any ideas or thoughts.

Regards.

My python code follows :

# -*- coding: utf-8 -*-
# modules nécessaires
from os import chdir
import MySQLdb as mdb
import sys
import datetime

# variables pour la BDD
serveur = ''
etat = ''
regle = ''
directive = ''

# répertoire où se trouve les logs
chdir("c:/Users/pidlas/Documents/rudder/")

def extract(log):
for ligne in log:
debut_date = ligne.find('[')
fin_date = ligne.find(']')
date = ligne[debut_date+1:fin_date-5]
date = datetime.datetime.strptime(date, "%Y-%m-%d %H:%M:%S")
sep_N = ligne.find('N:')
serveur = ligne[ligne.find('[',sep_N)+1:ligne.find(']',sep_N)]
sep_S = ligne.find('S:')
etat = ligne[ligne.find('[',sep_S)+1:ligne.find(']',sep_S)]
sep_R = ligne.find('R:')
regle = ligne[ligne.find('[',sep_R)+1:ligne.find(']',sep_R)]
sep_D = ligne.find('D:')
directive = ligne[ligne.find('[',sep_D)+1:ligne.find(']',sep_D)]

try:
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 (serveur, id_etat, id_directive, id_regle, date)
SELECT id_serveur, id_etat, id_directive, id_regle 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()

info.close()
db.close()
return logfile

if __name__ == "__main__":
with open('non-compliant-reports.log','r') as info:
extract(info)

Options: ReplyQuote


Subject
Written By
Posted
TypeError when inserting a date
April 25, 2013 03:35AM


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.