CODEWITHSUNDEEP
pythonmysqlcsv
john
john

Reputation: 11679

TypeError: not enough arguments for format string while inserting into mysql database

I have a csv file like this:

[email protected], 01-05-2014
[email protected], 01-05-2014
[email protected], 01-05-2014
[email protected], 01-05-2014

I am reading the above csv file and extracting domain name and also the count of emails address by domain name and date as well. All these things I need to insert into MySQL table called domains.

Below is the code in which is giving me error as TypeError: not enough arguments for format string and it's happening when I try to insert into domains table.

#!/usr/bin/python
import fileinput
import csv
import os
import sys
import time
import MySQLdb

from collections import defaultdict, Counter

domain_counts = defaultdict(Counter)

# ======================== Defined Functions ======================
def get_file_path(filename):
    currentdirpath = os.getcwd()  
    # get current working directory path
    filepath = os.path.join(currentdirpath, filename)
    return filepath
# ===========================================================
def read_CSV(filepath):

    with open('emails.csv') as f:
        reader = csv.reader(f)
        for row in reader:
            domain_counts[row[0].split('@')[1].strip()][row[1]] += 1

    db = MySQLdb.connect(host="localhost", # your host, usually localhost
                         user="root", # your username
                         passwd="abcdef1234", # your password
                         db="test") # name of the data base
    cur = db.cursor()

    q = """INSERT INTO domains(domain_name, cnt, date_of_entry) VALUES(%s, %s, STR_TO_DATE(%s, '%d-%m-%Y'))"""

    for domain, data in domain_counts.iteritems():
        for email_date, email_count in data.iteritems():
             cur.execute(q, (domain, email_count, email_date))

    db.commit()

# ======================= main program =======================================
path = get_file_path('emails.csv') 
read_CSV(path) # read the input file

What is wrong I am doing?

As of now my data type for date_of_entry column is date in MySQL.

Upvotes: 1

Views: 916

Answers (3)

J.Lehmann
J.Lehmann

Reputation: 71

You need the "%d-%m-%Y" in your sql statement in exact this way. But python (or the execute command) tries first to use it for string formatting and throws this error.

I think you have to escape it and you should try following:

q = """INSERT INTO domains(domain_name, cnt, date_of_entry) VALUES(%s, %s, STR_TO_DATE(%s, '%%d-%%m-%%Y'))"""

Upvotes: 2

David Soussan
David Soussan

Reputation: 2736

It is detecting the %s as a format string and failing on that. You need to surround it with quotes I guess

INSERT INTO domains(domain_name, cnt, date_of_entry) VALUES(%s, %s, STR_TO_DATE('%s', '%d-%m-%Y'))

Upvotes: 0

Mate Hegedus
Mate Hegedus

Reputation: 2905

Try this:

q = """INSERT INTO domains(domain_name, cnt, date_of_entry) VALUES(%s, %s, STR_TO_DATE(%s, 'd-m-Y'))"""

So we have changed from STR_TO_DATE(%s, '%d-%m-%Y')) to STR_TO_DATE(%s, 'd-m-Y'))

Upvotes: 0

Related Questions