How can I insert data into a MySQL database?

Question

I want to insert the integers 188 and 90 in my MySQL database, but the following code doesn't work:

import MySQLdb conn = MySQLdb.connect(host= "localhost",                   user="root",                   passwd="newpassword",                   db="engy1") x = conn.cursor() x.execute("SELECT *  FROM anooog1") x.execute (" INSERT INTO anooog1 VALUES ('%s','%s') ", (188,90)) row = x.fetchall() 

Why doesn't it work?

Answer 1

#Server Connection to MySQL:  import MySQLdb conn = MySQLdb.connect(host= "localhost",                   user="root",                   passwd="newpassword",                   db="engy1") x = conn.cursor()  try:    x.execute("""INSERT INTO anooog1 VALUES (%s,%s)""",(188,90))    conn.commit() except:    conn.rollback()  conn.close() 

edit working for me:

>>> import MySQLdb >>> #connect to db ... db = MySQLdb.connect("localhost","root","password","testdb" ) >>>  >>> #setup cursor ... cursor = db.cursor() >>>  >>> #create anooog1 table ... cursor.execute("DROP TABLE IF EXISTS anooog1") __main__:2: Warning: Unknown table 'anooog1' 0L >>>  >>> sql = """CREATE TABLE anooog1 ( ...          COL1 INT,   ...          COL2 INT )""" >>> cursor.execute(sql) 0L >>>  >>> #insert to table ... try: ...     cursor.execute("""INSERT INTO anooog1 VALUES (%s,%s)""",(188,90)) ...     db.commit() ... except:      ...     db.rollback() ...  1L >>> #show table ... cursor.execute("""SELECT * FROM anooog1;""") 1L >>> print cursor.fetchall() ((188L, 90L),) >>>  >>> db.close() 

table in mysql;

mysql> use testdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A  Database changed mysql> SELECT * FROM anooog1; +------+------+ | COL1 | COL2 | +------+------+ |  188 |   90 | +------+------+ 1 row in set (0.00 sec)  mysql>  

Answer 2

Here is OOP:

import MySQLdb   class Database:      host = 'localhost'     user = 'root'     password = '123'     db = 'test'      def __init__(self):         self.connection = MySQLdb.connect(self.host, self.user, self.password, self.db)         self.cursor = self.connection.cursor()      def insert(self, query):         try:             self.cursor.execute(query)             self.connection.commit()         except:             self.connection.rollback()        def query(self, query):         cursor = self.connection.cursor( MySQLdb.cursors.DictCursor )         cursor.execute(query)          return cursor.fetchall()      def __del__(self):         self.connection.close()   if __name__ == "__main__":      db = Database()      #CleanUp Operation     del_query = "DELETE FROM basic_python_database"     db.insert(del_query)      # Data Insert into the table     query = """         INSERT INTO basic_python_database         (`name`, `age`)         VALUES         ('Mike', 21),         ('Michael', 21),         ('Imran', 21)         """      # db.query(query)     db.insert(query)      # Data retrieved from the table     select_query = """         SELECT * FROM basic_python_database         WHERE age = 21         """      people = db.query(select_query)      for person in people:         print "Found %s " % person['name']