CODEWITHSUNDEEP
pythonmysqlmysql-python
Rorschach
Rorschach

Reputation: 3802

Building a class in python with mysqldb

I am making a database class in python to help with managing my mysql database.

The code below works until the last line

import MySQLdb

class Database:
    ...

    def insert(self, query):
        try:
            self.cursor.execute(query)
            self.connection.commit()
        except:
            self.connection.rollback()
    ...

db = Database()
db.insert('INSERT INTO items (title) VALUES ("tester2");')
mystring = "TEST"
db.insert('INSERT INTO items (title) VALUES (%s);', mystring)

The last line causes the error:

    db.insert('INSERT INTO items (title) VALUES (%s);', mystring)
TypeError: insert() takes exactly 2 arguments (3 given)

I have tried to rearrange the contents inside the db.insert's parens but I cannot figure out the syntax.

For instance I have tried this as well, but it neither inserts nor gives an error:

db.insert('''"INSERT INTO items (title) VALUES (%s);", mystring''')

My question is can I get the syntax right in order to insert using the %s, or do I have to change my class's insert function?

EDIT 1

I have tried the suggested lines:

db.insert("INSERT INTO items (title) VALUES (%s);"% mystring)

and

db.insert('INSERT INTO items (title) VALUES {value};'.format(value=mystring))

neither gave errors nor inputed any value into the database

EDIT 2

Whole code for main that calls the Database class:

if name == "main":

db = Database()
mystring = 'Tester1'
mystring2 = 'Tester2'
db.insert('INSERT INTO items (title) VALUES ("Tester3");')  #works
db.insert("INSERT INTO items (title) VALUES (%s);" % mystring) #does not work
db.insert('INSERT INTO items (title) VALUES {value};'.format(value=mystring2)) #does not work
print db.query('SELECT * FROM items;')

Upvotes: 1

Views: 300

Answers (2)

Haifeng Zhang
Haifeng Zhang

Reputation: 31895

Edit:

The issue you have is not only passing one argument. but also invoking execute() incorrectly:

insert_stat ="INSERT INTO employees (emp_no, first_name, last_name, hire_date) VALUES (%s, %s, %s, %s)"
data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
# execute() takes 2 arguments, first is the statement as above, second argument is a tuple which contains all values
cursor.execute(insert_stmt, data)

In your case, it should be something look like: self.cursor.execute('INSERT INTO items (title) VALUES (%s);', (mystring,))

Original Answer:

You need to pass only one argument query. Therefore you can format your query as one string like this:

'INSERT INTO items (title) VALUES {value};'.format(value=mystring)

change db.insert('INSERT INTO items (title) VALUES (%s);', mystring) to db.insert('INSERT INTO items (title) VALUES {value};'.format(value=mystring))

Upvotes: 3

Bahrom
Bahrom

Reputation: 4862

Try this: db.insert("INSERT INTO items (title) VALUES (%s);" % mystring) Basically you want mystring to be part of the query string, not a separate parameter.

Upvotes: 0

Related Questions