Unable to insert into MySQL database

Question

I am trying to insert something in the MySQL database using the following code, and even though I am not getting any exception or errors, there's nothing in the Table user. In crusor.execute() I tried encapsulating the column names with single quotes and without also using the backtacks nothing worked for me.

import MySQLdb as sql

class DataBaseInteraction:
    def __init__(self):
        shost = "127.0.0.1"
        suser = "root"
        spassword = ""
        sdb = "gui"
        connection = sql.connect(host=shost,
                         user=suser,
                         password=spassword,
                         db=sdb)

        try:
            self.cursor = connection.cursor()
            print("sucksess")
        except Exception as e:
            print("The Exception was" + str(e))

        self.createuser("UserName", "Name", "Email", "Password")

    def createuser(self, username, namee, password, email):
        print("reached here")
        try:
            self.cursor.execute("""INSERT INTO user (`UserName`, `Name`, `Email`, `Password`) VALUES ({},{},{},{})""".format(username,
                                                                                                                        namee,
                                                                                                                        email,
                                                                                                                        password))
            print("SuckSess")
        except Exception as e:
            print("Exception was "+str(e))


if __name__ == "__main__":
    a = DataBaseInteraction()

Here's the query I used to make the table

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `UserName` varchar(255) NOT NULL,
  `Name` varchar(255) NOT NULL,
  `Email` varchar(255) NOT NULL,
  `Password` varchar(255) NOT NULL,
  `CreationDate` date DEFAULT NULL,
  `LoggedIn` tinyint(1) NOT NULL DEFAULT '0',
  `LatestLoginTime` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

score 2 · Accepted Answer · answered Mar 14 '18 at 11:19

2

You are not committing the transaction. After self.cursor.execute statement do self.connection.commit() or set autocommit=True at the time of creating connection. Ex:

connection = sql.connect(host=shost,
                     user=suser,
                     password=spassword,
                     db=sdb,
                     autocommit=True)

answered Mar 14 '18 at 11:19

Gaur93

685
7
19

Thank you, I do seem to have visible entries on the table however, the columns however are empty. I changed the values that I am passing to the `createuser` function to `self.createuser("llama", "sahil", "thisismyemail.com", "passwordissecrect")` and now if I run the code the error I get is `Exception was (1054, "Unknown column 'llama' in 'field list'")`. But I am not passing llama as a column I am passing it as a value. Do you have any idea what could have caused this? – Sahil Mar 14 '18 at 11:34
I got it to work, the 5th answer [here](https://stackoverflow.com/questions/1346209/unknown-column-in-field-list-error-on-mysql-update-query) explained what I was doing wrong. – Sahil Mar 14 '18 at 12:09

Unable to insert into MySQL database

1 Answers1