I would like some advice why this would not insert data into my SQL table

Question

Following is my code:

import MySQLdb
def insert_popularity(PersonNumber, Category, Value):

# make a connection to the dataabse
connection = MySQLdb.connect(host='localhost', user='root', \
                             passwd='password', db='inb104')

# get a cursor on the database
cursor = connection.cursor()

# construct the SQL statement
sql = ("""INSERT INTO popularity (PersonNumber, Category, Value)
        VALUES(%s, %s, %s)""", (number, category, data))

def open_file(filename):        
    txt_file = file(filename, 'r')
    for line in txt_file:
        # Split the line on whitespace
        for value in line.split():                
            return value

            number = value[0]
            data = value[1]

# execute the query
cursor.execute(sql)

# commit the changes to the database\
connection.commit()

# close the cursor and connection
cursor.close()

connection.close()

Update:

After changing my code as per Paulo's suggestion I now get this error:

query() argument 1 must be string or read-only buffer, not tuple.

I am not sure what it is after trying to change my code:

def insert_popularity(Category, filename, cursor):

    txt_file = file(filename, 'r')
    for line in txt_file:
            # Split the line on whitespace
        number, value = line.split()
            # construct the SQL statement
        sql = ("""INSERT INTO popularity (PersonNumber, Category, Value)
                VALUES(%s, %s, %s)""", (number, Category, value))
            # execute the query
        cursor.execute(sql)

connection = MySQLdb.connect(host='localhost', user='root', \
                                 passwd='password', db='dogs')

cursor = connection.cursor()

Category = 'dogs'
insert_popularity(Category, 'dogs.txt', cursor)
connection.commit()
cursor.close()
connection.close()

no error message just when i look in the database there is no data — Alana, May 27 '11 at 06:03
possible duplicate of [Error in SQLite query](http://stackoverflow.com/questions/6148987/error-in-sqlite-query) — , May 28 '11 at 14:12

Thijs · Answer 1 · 2011-06-15T07:57:54.917

2

You've created the query to execute as a tuple. There two possibilities to solve this:

Use the created query (sql) as a list of arguments:

sql = ("""INSERT INTO popularity (PersonNumber, Category, Value) VALUES(%s, %s, %s)""", (number, Category, value)) # execute the query cursor.execute(*sql)
Directly add the query to the execute method:

cursor.execute("""INSERT INTO popularity (PersonNumber, Category, Value) VALUES(%s, %s, %s)""", (number, Category, value))

Number 2 is definitely a better option than the first one. Thanks to all comments!

edited Jun 15 '11 at 07:57

answered May 27 '11 at 07:25

Thijs

29
4

-1 on your first possibility because (1) `sql` applied to a tuple is a misnomer (2) it's byzantine putting a *known* number of args into a tuple (`sql`) and then immediately calling a method with *sql (3) you haven't read your answer (it needs two line breaks) – John Machin May 27 '11 at 08:18
could you please re word that im not sure what you mean? – Alana May 27 '11 at 08:23
@Ryan: If your request is addressed to me: "Do not use Thijs's first 'possibility'." – John Machin May 27 '11 at 08:33

John Machin · Answer 2 · 2011-05-27T08:29:03.853

2

Just do it simply, one thing at a time, no fancy stuff that is error prone and slows the reader down while they navigate the obfuscation:

sql = """INSERT INTO popularity (PersonNumber, Category, Value) VALUES (%s, %s, %s)"""
args = (number, Category, value)
cursor.execute(sql, args)

Your comment (execute the query) went away because (a) it was wrong (insert != query) and (b) the fixed version (execute the insertion) would be quite redundant given the clarity of the fixed code.

Update after new problem (too many values to unpack):

Instead of this code:

for line in txt_file:
        # Split the line on whitespace
    number, value = line.split()

do this:

for lino, line in enumerate(txt_file, 1):
    pieces = line.split()
    if len(pieces) != 2:
        print "Bad data in line %d: %r" % (lino, pieces)
        continue
    number, value = pieces

edited May 27 '11 at 08:29

answered May 27 '11 at 08:02

John Machin

81,303
11
141
189

thanks for that. but now it is saying that there are too many values to unpack when im spliting the line. how would i shorten that? – Alana May 27 '11 at 08:16
@ryan: Fix your data; you are expecting two pieces of whitespace-separated data per line, but at least one line has 3 or more such pieces. – John Machin May 27 '11 at 08:22
is threre a way to slip at the first whitespace so all the following data would go into the value variable? – Alana May 27 '11 at 08:35
@ John: it says enumerate() takes exactly 1 argument and 2 are given – Alana May 27 '11 at 08:42
@Ryan: Hard to tell what you fixed. Stop after 1st whitespace: `line.split(None, 1)`. enumerate problem: You are using Python 2.5 or earlier. If you can't upgrade (to 2.7), write code to count lines yourself -- you'll still a line number to report other problems :) – John Machin May 27 '11 at 09:00

score 1 · Answer 3 · answered May 27 '11 at 05:52

1

What are the data types of the number, category, and data? If any of these are strings, then you should wrap them in single quotes in your query.

answered May 27 '11 at 05:52

Cory Gagliardi

770
1
8
13

number is an int and catgory and data are strings – Alana May 27 '11 at 05:59
The good database libraries will handle the proper quoting of variables based on type. – Paulo Scardine May 27 '11 at 06:21

Paulo Scardine · Answer 4 · 2011-05-27T06:05:32.907

1

Do not take me wrong, but the code is very messed up...

the return inside the for loop will return the first splited string in the first line.
open_file is defined but never called

and so on...

My take would be something like:

def process_file(category, filename, cursor):
    txt_file = file(filename, 'r')
    for line in txt_file:
        number, value = line.split()
        sql = ("""INSERT INTO popularity (PersonNumber, Category, Value)
            VALUES(%s, %s, %s)""", (number, category, data))
        cursor.execute(sql)

connection = MySQLdb.connect(host='localhost', user='root',
                         passwd='password', db='inb104')
# get a cursor on the database
cursor = connection.cursor()
category = 'foo'
process_file(category, 'somefile.txt', cursor)
# commit the changes to the database\
connection.commit()
# close the cursor and connection
cursor.close()
connection.close()

edited May 27 '11 at 06:05

answered May 27 '11 at 05:54

Paulo Scardine

73,447
11
124
153

haha yeah sorry im very new to programing... its ok i will just try to make it work somehow – Alana May 27 '11 at 05:57
ahh so are you ment to execute the sql statement within the function? – Alana May 27 '11 at 06:10
Python is a great first language. Try to finish the tutorial at http://docs.python.org/tutorial/, it really pays up! – Paulo Scardine May 27 '11 at 06:15
@Ree, if you intend to insert a record for each line in the file then yes, you have to execute the insert inside the loop. – Paulo Scardine May 27 '11 at 06:19

score 1 · Answer 5 · answered May 28 '11 at 13:49

Try it like this:

def insert_popularity(Category, filename, cursor):
    sql = """INSERT INTO popularity (PersonNumber, Category, Value)
           VALUES(%s, %s, %s)"""

    txt_file = file(filename, 'r')
    for line in txt_file:
        # Split the line on whitespace
        number, value = line.split()
        # execute the query
        cursor.execute(sql, (number, Category, value))
    txt_file.close()

connection = MySQLdb.connect(host='localhost', user='root', \
                                 passwd='password', db='dogs')

cursor = connection.cursor()

Category = 'dogs'
insert_popularity(Category, 'dogs.txt', cursor)

connection.commit()
cursor.close()
connection.close()

Also note: your code suggests this is a MySQL database; if it's an SQLite database, like the title of your question says, please substitute '?' for every '%s' in the sql statement.

I would like some advice why this would not insert data into my SQL table

5 Answers5

Linked