Machine Learning Tutorials, Courses and Certifications
Related Articles
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.")
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.")
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)
