Tuesday , March 19 2024
Python MySQL Insert Into Table

Python MySQL Insert Into Table

4 Python MySQL Insert Into Table

Python MySQL Insert Into Table

To fill a table in MySQL, use the “INSERT INTO” statement.

In [8]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
name = input("Enter Name:")
address = input("Enter Address:")
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = (name,address)
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")
Enter Name:Karan
Enter Address:Ludhiana
1 record inserted.

Note: The statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.

Insert Multiple Rows

  • To insert multiple rows into a table, use the executemany() method.
  • The second parameter of the executemany() method is a list of tuples, containing the data you want to insert:
In [9]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Kevin', 'Dublin 4'),
  ('Keisha', 'Otawa 4'),
  ('Varun', 'Ontario'),
  ('Priya', 'Delhi'),
  ('Atharv', 'Goa'),
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")
5 record inserted.

Get the last Inserted ID

  • You can get the id of the row you just inserted by asking the cursor object.
In [10]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
name = input("Enter Name:")
address = input("Enter Address:")
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = (name,address)
mycursor.execute(sql, val)
mydb.commit()
print("1 record inserted, ID:", mycursor.lastrowid)
Enter Name:Sia
Enter Address:Dubai
1 record inserted, ID: 7

About Machine Learning

Check Also

Python MySQL Select From

Python MySQL Select From

5 Python MySQL Select From Python MySQL Select From Table¶To select from a table in …

Leave a Reply

Your email address will not be published. Required fields are marked *