If you could insert information right into a MySQL desk utilizing Python, then look no additional.
If you could first be taught in regards to the mysql.connector and the way to 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",
consumer = "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, "document efficiently inserted")
This can insert a single row into the MySQL desk.
Word 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 could insert a number of rows on the similar time, then we’ve got a greater possibility for you.
Tips on how 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 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 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, observe that we all the time name the .commit() as soon as we’re performed.
Tips on how 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 sometimes performed by creating an id column and assigning an auto_increment to the column.
This fashion incremental id numerals shall 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 clients (title, deal with) 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 by utilizing the .commit() after which name the mycursor.lastrowid which incorporates the worth of the final inserted row’s id.
