Building a class in python with mysqldb

Question

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;')

@haifzhan you're right, fixed it, (it was correct in my code) — Rorschach
– Rorschach, Commented Mar 22, 2016 at 22:39
@Rorschach see my edit. you have to invoke execute correctly — Haifeng Zhang
– Haifeng Zhang, Commented Mar 22, 2016 at 22:44
can you print the exception you're getting change your exception to except Exception as e and add print e before rollback — Bahrom
– Bahrom, Commented Mar 22, 2016 at 22:46

Haifeng Zhang · Accepted Answer · 2016-03-29 20:38:32Z

3

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

edited Mar 29, 2016 at 20:38

answered Mar 22, 2016 at 22:34

Haifeng Zhang

32.1k21 gold badges95 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bahrom · Accepted Answer · 2016-03-22 22:34:43Z

0

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.

edited Mar 22, 2016 at 22:34

answered Mar 22, 2016 at 22:31

Bahrom

4,87235 silver badges44 bronze badges

2 Comments

Rorschach Over a year ago

that gave: db.insert('"INSERT INTO items (title) VALUES (%s);"% mystring) ^ SyntaxError: EOL while scanning string literal

Bahrom Over a year ago

Whoops extra quote, sorry on the phone, remove first quote. See updated answer.

Collectives™ on Stack Overflow

Building a class in python with mysqldb

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related