How can I insert data into a MySQL database?
#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>
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']