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

User · Answer

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  |

User · Answer

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       quot   main   quot        db   Database         CleanUp Operation     del query    quot DELETE FROM basic python database quot      db insert del query         Data Insert into the table     query    quot  quot  quot          INSERT INTO basic python database           name    age           VALUES           Mike   21             Michael   21             Imran   21           quot  quot  quot         db query query      db insert query         Data retrieved from the table     select query    quot  quot  quot          SELECT   FROM basic python database         WHERE age   21          quot  quot  quot       people   db query select query       for person in people          print  quot Found  s  quot    person  name

User · Answer

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    gt  gt  gt  import MySQLdb  gt  gt  gt   connect to db     db   MySQLdb connect  localhost   root   password   testdb     gt  gt  gt    gt  gt  gt   setup cursor     cursor   db cursor    gt  gt  gt    gt  gt  gt   create anooog1 table     cursor execute  DROP TABLE IF EXISTS anooog1     main   2  Warning  Unknown table  anooog1  0L  gt  gt  gt    gt  gt  gt  sql      CREATE TABLE anooog1                COL1 INT                 COL2 INT       gt  gt  gt  cursor execute sql  0L  gt  gt  gt    gt  gt  gt   insert to table     try          cursor execute    INSERT INTO anooog1 VALUES   s  s      188 90           db commit       except               db rollback        1L  gt  gt  gt   show table     cursor execute    SELECT   FROM anooog1      1L  gt  gt  gt  print cursor fetchall     188L  90L     gt  gt  gt    gt  gt  gt  db close     table in mysql   mysql gt  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 gt  SELECT   FROM anooog1   ------ ------    COL1   COL2    ------ ------     188     90    ------ ------  1 row in set  0 00 sec   mysql gt

[python] How can I insert data into a MySQL database?

Examples related to python

Examples related to mysql