Как я могу вставить данные в базу данных MySQL?
Я хочу вставить целые числа 188 и 90 в мою базу данных MySQL, но следующий код не работает:
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()
почему это не работает?
2 ответа:
#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 работает на меня:
>>> 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()
таблица в 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>
вот ООП:
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']