Python MySQL – Insert into Table

To Insert into Tables

This is the python insert multiple rows module of our entire Python MySQL tutorial. And if you wish to fill up any particular table in MySQL then you can do that by using the “INSERT INTO” statement. For example, if you wish to insert some kind of record in a table named ‘customers’ then  

import mysql.connector

mydb = mysql.connector.connect (

  host=“localhost”

  user=“yourusername”

 passwd=“yourpassword”

 database=“mydatabase”

)

mycursor = mydb.cursor ( )

sql = “INSERT INTO customers ( name, address ) VALUES (%s, %s)”

val = ( “John”, “Highway 21” )

mycursor.execute ( sql, val )

mydb.commit ( )

print ( mycursor.rowcount, “record inserted.” )

  If you look carefully at the above example of insert query in mysql then you can probably notice the statement of ‘mydb.commit ( )’. It is important for you to know that this statement is used to make the necessary changes to the table. If you forget to use this statement then no changes will be made to the table.  

To Insert Multiple Rows

If you wish to insert a large number of rows in a table then you can do that too by using the executemany ( ) method. This method has quite a few parameters and the second parameter of this method of insert query in mysql is actually a list of tuples. This list of tuples contains the data that you would want to actually insert. If you wish to fill the table named ‘customers’ with data then the mysql insert example for this is mentioned below.  

import mysql.connector

mydb = mysql.connector.connect (

  host=“localhost”

  user=“yourusername”

 passwd=“yourpassword”

 database=“mydatabase”

)

mycursor = mydb.cursor ( )

sql = “INSERT INTO customers ( name, address ) VALUES (%s, %s)”

val = [

  ( ‘Peter’, ‘Lowstreet 4’ ) ,

  ( ‘Amy’, ‘Apple st 652’ ) ,

  (‘Hannah’, ‘Mountain 21’ ) ,

  ( ‘Michael’, ‘Valley 345’ ) ,

  ( ‘Sandy’, ‘Ocean blvd 2’ ) ,

  ( ‘Betty’, ‘Green Grass 1’ ) ,

  ( ‘Richard’, ‘Sky st 331’ ) ,

  ( ‘Susan’, ‘One way 98’ ) ,

  ( ‘Vicky’, ‘Yellow Garden 2’ ) ,

  ( ‘Ben’, ‘Park Lane 38’ ) ,

  ( ‘William’, ‘Central st 954’ ) ,

  ( ‘Chuck’, ‘Main Road 989’ ) ,

  ( ‘Viola’, ‘Sideway 1633’ ) ,

]

mycursor.execute ( sql, val )

mydb.commit ( )

print ( mycursor.rowcount, “was inserted.” )

  To Get the Inserted ID If you wish to get the ID of the particular row that you inserted then you can do that by asking the particular cursor object. It is also important for you to know that if you had inserted more than a single row then the ID for the row that you inserted last will be returned. The mysql insert example for this is mentioned below.  

import mysql.connector

mydb = mysql.connector.connect (

  host=“localhost”

  user=“yourusername”

 passwd=“yourpassword”

 database=“mydatabase”

)

mycursor = mydb.cursor ( )

sql = “INSERT INTO customers ( name, address ) VALUES (%s, %s)”

val = ( “Michael”, “Blue Village” )

mycursor.execute ( sql, val )

mydb.commit ( )

print ( “1 record inserted, ID : ”, mycursor.lastrowid )

With this, we finish the Python insert multiple rows part of our Python MySQL Tutorial.