The best way to Insert right into a MySQL Desk in Python

If you want to insert information right into a MySQL desk utilizing Python, then look no additional.

If you want to first be taught concerning the mysql.connector and how you can get this up and operating, first check out the The best way to Set up MySQL Driver in Python put up 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 (title, deal with) VALUES (%s, %s)"
val = ("Andrew", "Someplace good")
mycursor.execute(sql, val)

mydb.commit()

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

It will insert a single row into the MySQL desk.

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

If you want to insert a number of rows on the identical time, then we’ve a greater choice for you.

The best way 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 (title, deal with) 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 system, we will insert a number of rows in the identical question. This reduces the quantity of connections to the database and accelerates commit time.

Talking of commit, notice that we all the time name the .commit() as soon as we’re carried out.

The best way to get the Final Inserted ID from MySQL in Python

Usually you’ll want to get the final row ID, also called the row that you simply simply inserted’s ID.

That is usually carried out 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 (title, deal with) 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 through the use of the .commit() after which name the mycursor.lastrowid which accommodates the worth of the final inserted row’s id.