Python MySQL Insert Into Table


Python Insert Data Into Mysql Table

After creating a database and a table, we can begin adding information.

The following are some rules to follow in terms of syntax :

  • In Python the SQL query has to be quoted.
  • You need to quotes string values (text columns) inside the SQL query.
  • You don't need to quotes for numerical values.
  • The name NULL and DEFAULT keywords are cannot be quoted.

Use the INSERT INTO command to add new records into a MySQL table.

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

We established an empty table called "books" with three columns : "id", "name", and "price" in the previous chapter.

Look at the table structure:

Column Name Datatype Extra
id int auto_increment + primary key
name text
price float

Note :- If there's AUTO INCREMENT column (like the column ID), the SQL query shouldn't have to specify it; MySQL adds the value automatically.


Insert Into Table

The execute() method is used to run any sql query against mysql database.

The rowcount property is used to return number of affected rows executed by last queryies.

The commit() method is used to save all changes made by sql queries like insert, update, delete records and so on.

Note:- You cannot get any changes (record insert, update, and delete) in the table, If you omit the commit() method.

Example :- Insert a record into "books" table :

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

# Insert a record using normal sql query

sql = "INSERT INTO books (name, price) VALUES ('python cookbook', 175.50)"
mycursor.execute(sql)

print(mycursor.rowcount, "record inserted.")

# Insert a record using prepared statement sql query (Recommended)
# It gives more security compared to above one

qry = "INSERT INTO books (name, price) VALUES (%s, %s)"
val = ("python complete reference", "220")
mycursor.execute(qry, val)

print(mycursor.rowcount, "record inserted.")

mydb.commit()

Output :-

1 record inserted.
1 record inserted.

After executing the above program the "books" table look like:

ID Name Price
1 python cookbook 175.50
2 python complete reference 220.00

Important!: Please note: mydb.commit(), Changes must be made, otherwise the table cannot be changed.

You can also search for these topics, python mysql insert into table example, insert into multiple table using python, insert mysql select table using python, python key value and null value of the mysql insert into table, working of python mysql insert into table, check the insert into table exists in python, define the python insert table range.

Insert Multiple Rows

The executemany() method is used to insert multiple records at once into a table.

The second parameter of the method executemany() is a tuple list that includes the data that are to be inserted :

Example :- Insert three records into "books" table with data :

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO books (name, price) VALUES (%s, %s)"
val = [
 ('Python Networking', 160),
 ('Web Programming', 70),
 ('Web with Python', 120)
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "record was inserted.")

Output :-

3 record was inserted.

You can also search for these topics, python insert multiple rows get difference between, multiple rows number in python, how to use multiple rows replace in python, python remove and rewite the multiple rows, request the multiple rows value using insert, Example for python insert multiple rows.

Get Last Inserted ID

By inquiring the cursor object you may acquire the id of the row you just inserted.

The lastrowid property is used to return last inserted id from the table.

Note : The id of the latest inserted rows is returned if more than one row is inserted.

Example :- Get last inserted id :

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="myusername",
  password="mypassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO books (name, price) VALUES (%s, %s)"
val = ("New era of python", 55)
mycursor.execute(sql, val)

mydb.commit()

print("ID: ", mycursor.lastrowid)

Output :-

ID: 6

You can also search for these topics, python get inserted id element, how to group the inserted get id in python, network of the python to get the inserted id, execute the get inserted id name using python, process the python to get the id which is to be inserted, python to get inserted id user unique, how to zip the file to insert the get python id.