Python MySQL – Insert into Table
To insert a record into MySQL table in Python,
- Create a connection to the MySQL database with user credentials and database name, using connect() function.
- Get cursor object to the database using cursor() function.
- Take INSERT INTO table query, and values tuple for the record.
- Call execute() function on the cursor object, and pass the query string, and values tuple as arguments to execute() function.
Consider that there is a schema named mydatabase in MySQL. The credentials to access this database are: user – root and password – admin1234, and there is a table named fruits in mydatabase.
In the following program, we get the first record from fruits table.
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="admin1234", database="mydatabase" ) mycursor = mydb.cursor() sql = "INSERT INTO fruits (name, quantity, country) VALUES (%s, %s, %s)" val = ("Apple", 25, "Canada") try: mycursor.execute(sql, val) print(mycursor.rowcount, "record inserted.") except: print('Something went wrong.') mydb.commit()
1 record inserted.
Table before inserting record
Table after inserting record
In this tutorial of Python Examples, we learned how to insert a record into table in MySQL database from a Python program.