If you should insert knowledge right into a MySQL desk utilizing Python, then look no additional.
If you should first study concerning the mysql.connector and the best way to get this up and operating, first check out the How one can 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 prospects (identify, handle) 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.
Be aware the significance of the .commit() as soon as we’ve got executed our SQL assertion. That is to persist it to the database.
If you should insert a number of rows on the similar time, then we’ve got a greater possibility for you.
How one can 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 prospects (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 hastens commit time.
Talking of commit, word that we all the time name the .commit() as soon as we’re finished.
How one can get the Final Inserted ID from MySQL in Python
Typically it would be best to get the final row ID, often known 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",
person = "username",
password = "YoUrPaSsWoRd",
database = "your_database"
)
mycursor = mydb.cursor()
sql = "INSERT INTO prospects (identify, handle) 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 comprises the worth of the final inserted row’s id.
