If you want to insert knowledge right into a MySQL desk utilizing Python, then look no additional.
If you want to first study concerning the mysql.connector and methods to get this up and working, first check out the The right 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 prospects (title, handle) VALUES (%s, %s)"
val = ("Andrew", "Someplace good")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "document efficiently inserted")
This may insert a single row into the MySQL desk.
Observe 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 want to insert a number of rows on the similar time, then we’ve got a greater possibility for you.
The right 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 prospects (title, 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 system, we will insert a number of rows in the identical question. This reduces the quantity of connections to the database and quickens commit time.
Talking of commit, notice that we all the time name the .commit() as soon as we’re achieved.
The right way to get the Final Inserted ID from MySQL in Python
Usually you’ll want to get the final row ID, also referred to as the row that you simply simply inserted’s ID.
That is usually achieved by creating an id column and assigning an auto_increment to the column.
This fashion incremental id numerals will probably 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 (title, handle) VALUES (%s, %s)"
val = ("Brad", "Los Angeles")
mycursor.execute(sql, val)
mydb.commit()
print("1 document 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.
