Methods to Insert right into a MySQL Desk in Python

If you must insert knowledge right into a MySQL desk utilizing Python, then look no additional.

If you must first study in regards to the mysql.connector and how you can get this up and working, first check out the Methods to Set up MySQL Driver in Python publish earlier than persevering with.

How do Insert right into a MySQL Desk in Python

import mysql.connector

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

mycursor = mydb.cursor()

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

mydb.commit()

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

This may insert a single row into the MySQL desk.

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

If you must insert a number of rows on the identical time, then we’ve a greater possibility for you.

Methods to Insert A number of rows into MysQL utilizing Python

import mysql.connector

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

mycursor = mydb.cursor()

sql = "INSERT INTO prospects (identify, tackle) 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 are able to insert a number of rows in the identical question. This reduces the quantity of connections to the database and hastens commit time.

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

Methods to get the Final Inserted ID from MySQL in Python

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

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

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

import mysql.connector

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

mycursor = mydb.cursor()

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

mydb.commit()

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

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