Tips on how to Insert right into a MySQL Desk in Python

If it’s essential insert knowledge right into a MySQL desk utilizing Python, then look no additional.

If it’s essential first be taught concerning the mysql.connector and get this up and operating, first check out the Tips on how to Set up MySQL Driver in Python submit earlier than persevering with.

How do Insert right into a MySQL Desk in Python#

import mysql.connector

mydb = mysql.connector.join(
  host = "localhost",
  person = "username",
  password = "YoUrPaSsWoRd",
  database = "your_database"
)

mycursor = mydb.cursor()

sql = "INSERT INTO clients (identify, handle) VALUES (%s, %s)"
val = ("Andrew", "Someplace good")
mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "file efficiently inserted")

This can insert a single row into the MySQL desk.

Notice the significance of the .commit() as soon as we now have executed our SQL assertion. That is to persist it to the database.

If it’s essential insert a number of rows on the similar time, then we now have a greater choice for you.

Tips on how to Insert A number of rows into MysQL utilizing Python#

import mysql.connector

mydb = mysql.connector.join(
  host = "localhost",
  person = "username",
  password = "YoUrPaSsWoRd",
  database = "your_database"
)

mycursor = mydb.cursor()

sql = "INSERT INTO clients (identify, handle) VALUES (%s, %s)"
val = [
  ('Jack', 'New York'),
  ('Mary', 'Vancouver'),
  ('Peter', 'Cairo'),
  ('Sarah', 'Faro'),
  ('Stuart', 'London'),
  ('Hayley', 'Dubai')
]

mycursor.executemany(sql, val)

mydb.commit()

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

Utilizing this method, we will insert a number of rows in the identical question. This reduces the quantity of connections to the database and hurries up commit time.

Talking of commit, word that we at all times name the .commit() as soon as we’re accomplished.

Tips on how to get the Final Inserted ID from MySQL in Python#

Typically it would be best to get the final row ID, also referred to as the row that you simply simply inserted’s ID.

That is sometimes accomplished by creating an id column and assigning an auto_increment to the column.

This fashion incremental id numerals might be assigned to every row on row creation by default.

import mysql.connector

mydb = mysql.connector.join(
  host = "localhost",
  person = "username",
  password = "YoUrPaSsWoRd",
  database = "your_database"
)

mycursor = mydb.cursor()

sql = "INSERT INTO clients (identify, handle) VALUES (%s, %s)"
val = ("Brad", "Los Angeles")
mycursor.execute(sql, val)

mydb.commit()

print("1 file inserted, ID:", mycursor.lastrowid)

As soon as once more, we shut off through the use of the .commit() after which name the mycursor.lastrowid which incorporates the worth of the final inserted row’s id.