CODEWITHSUNDEEP
pythonsqltext-files
Alana
Alana

Reputation: 19

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

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()

Upvotes: 1

Views: 3307

Answers (5)

Thijs
Thijs

Reputation: 29

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

  1. 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)

  2. 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!

Upvotes: 2

tzot
tzot

Reputation: 96011

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.

Upvotes: 1

John Machin
John Machin

Reputation: 83002

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

Upvotes: 2

Paulo Scardine
Paulo Scardine

Reputation: 77359

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

  1. the return inside the for loop will return the first splited string in the first line.
  2. 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()

Upvotes: 1

Cory Gagliardi
Cory Gagliardi

Reputation: 780

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.

Upvotes: 1

Related Questions