MySQL is a Relational Database Management System (RDBMS) whereas the structured Query Language (SQL) is the language used for handling the RDBMS using commands i.e Creating, Inserting, Updating and Deleting the data from the databases. SQL commands are case insensitive i.e CREATE and create signify the same command.
Note: Before we insert data into our database, we need to create a table. In order to do so, refer to Python: MySQL Create Table.
Inserting data
You can insert one row or multiple rows at once. The connector code is required to connect the commands to the particular database.
Connector query
# Enter the server name in host # followed by your user and # password along with the database # name provided by you. import mysql.connector mydb = mysql.connector.connect( host = "localhost", user = "username", password = "password", database = "database_name") mycursor = mydb.cursor() |
Now, the Insert into Query can be written as follows:
Example: Let’s suppose the record looks like this –
sql = "INSERT INTO Student (Name, Roll_no) VALUES (%s, %s)"val = ("Ram", "85") mycursor.execute(sql, val) mydb.commit() print(mycursor.rowcount, "details inserted") # disconnecting from server mydb.close() |
Output:
1 details inserted
To insert multiple values at once, executemany() method is used. This method iterates through the sequence of parameters, passing the current parameter to the execute method.
Example:
sql = "INSERT INTO Student (Name, Roll_no) VALUES (%s, %s)"val = [("Akash", "98"), ("Neel", "23"), ("Rohan", "43"), ("Amit", "87"), ("Anil", "45"), ("Megha", "55"), ("Sita", "95")] mycursor.executemany(sql, val) mydb.commit() print(mycursor.rowcount, "details inserted") # disconnecting from server mydb.close() |
Output:
7 details inserted
Note:
- The
cursor()is used in order to iterate through the rows. - Without the command
mydb.commit()the changes will not be saved.
Recommended Posts:
- PostgreSQL - Insert Data Into a Table using Python
- Python MariaDB - Insert into Table using PyMySQL
- Connect MySQL database using MySQL-Connector Python
- Python: MySQL Create Table
- Python MySQL - Drop Table
- Display the Pandas DataFrame in table style and border around the table and not around the rows
- Python program to insert an element into sorted list
- Python | Convert an HTML table into excel
- Convert HTML table into CSV file in python
- Read SQL database table into a Pandas DataFrame using SQLAlchemy
- MySQL-Connector-Python module in Python
- List Methods in Python | Set 2 (del, remove(), sort(), insert(), pop(), extend()...)
- Python | Pandas Index.insert()
- numpy.insert() in Python
- MongoDB Python | Insert and Update Data
- MongoDB python | insert(), replace_one(), replace_many()
- Python list | insert()
- Python | Pandas dataframe.insert()
- Python | Insert the string at the beginning of all items in a list
- Python | Pandas TimedeltaIndex.insert
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.
