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?

When you try running this, do you get an error message? If so, please include it with your question. — Simon Nickerson
– Simon Nickerson, Commented Apr 16, 2011 at 16:09
no error i just need to know how to insert in the table using python — Joja Ingy
– Joja Ingy, Commented Apr 16, 2011 at 16:12

dting · Accepted Answer · 2011-04-16 17:34:10Z

135

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

edited Apr 16, 2011 at 17:34

answered Apr 16, 2011 at 16:21

dting

39.4k10 gold badges98 silver badges117 bronze badges

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

7 Comments

dting Over a year ago

@Joja Ingy what does your table look like? also remove the ' surrounding your your Values.

Joja Ingy Over a year ago

its and int table consist of two columns

Oki E. Rinaldi Over a year ago

tripple-double-quote ("""), why?

e-info128 Over a year ago

ImportError: No module named MySQLdb, with pip install: No matching distribution found for MySQLdb

Pavin Joseph Over a year ago

@OkiErieRinaldi I would hazard a guess that its a remnant from when the sql query wasn't parameterized and you had to protect against the errant " and '.

|

Community · Accepted Answer · 2020-06-20 09:12:55Z

40

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']

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 18, 2013 at 5:27

Imran Hossain

4194 silver badges3 bronze badges

2 Comments

YaddyVirus Over a year ago

why the triple double-quote?

FLAK-ZOSO Over a year ago

@YaddyVirus, a multiline string in Python is usually done this way.

Niroshan Ratnayake · Accepted Answer · 2020-11-15 08:09:23Z

This way worked for me when adding random data to MySql table using a python script.
First install the following packages using the below commands

pip install mysql-connector-python<br>
pip install random

import mysql.connector
import random

from datetime import date


start_dt = date.today().replace(day=1, month=1).toordinal()
end_dt = date.today().toordinal()

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="your_db_name"
)

mycursor = mydb.cursor()

sql_insertion = "INSERT INTO customer (name,email,address,dateJoined) VALUES (%s, %s,%s, %s)"
#insert 10 records(rows)
for x in range(1,11):
    #generate a random date
    random_day = date.fromordinal(random.randint(start_dt, end_dt))
    value = ("customer" + str(x),"customer_email" + str(x),"customer_address" + str(x),random_day)
    mycursor.execute(sql_insertion , value)

mydb.commit()

print("customer records inserted!")

Following is a sample output of the insertion

cid       |  name      |  email           |    address        |  dateJoined  |

1         | customer1  |  customer_email1 | customer_address1 |  2020-11-15  |
2         | customer2  |  customer_email2 | customer_address2 |  2020-10-11  |
3         | customer3  |  customer_email3 | customer_address3 |  2020-11-17  |
4         | customer4  |  customer_email4 | customer_address4 |  2020-09-20  |
5         | customer5  |  customer_email5 | customer_address5 |  2020-02-18  |
6         | customer6  |  customer_email6 | customer_address6 |  2020-01-11  |
7         | customer7  |  customer_email7 | customer_address7 |  2020-05-30  |
8         | customer8  |  customer_email8 | customer_address8 |  2020-04-22  |
9         | customer9  |  customer_email9 | customer_address9 |  2020-01-05  |
10        | customer10 |  customer_email10| customer_address10|  2020-11-12  |

Collectives™ on Stack Overflow

How can I insert data into a MySQL database?

3 Answers 3

7 Comments

Here is OOP:

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Here is OOP:

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related