Python MariaDB – Insert into Table using PyMySQL
MariaDB is an open source Database Management System and its predecessor to MySQL. The pymysql client can be used to interact with MariaDB similar to that of MySQL using Python.
In this article we will look into the process of inserting rows to a table of the database using pymysql. You can insert one row or multiple rows at once. The connector code is required to connect the commands to the particular database. To insert data use the following syntax:
Syntax: INSERT INTO table_name column1, column2 VALUES (value1, value2)
Note: The INSERT query is used to insert one or multiple rows in a table.
Example :
To insert one row in table PRODUCT.
Python3
import pymysql
Host = "localhost"
User = "user"
Password = ""
database = "database_name"
conn = pymysql.connect(host = Host, user = User, password = Password, database)
cur = conn.cursor()
PRODUCT_ID = '1201'
price = 10000
PRODUCT_TYPE = 'PRI'
query = f "INSERT INTO PRODUCT (PRODUCT_ID, price,PRODUCT_TYPE) VALUES ('{PRODUCT_ID}', '{price}', '{PRODUCT_TYPE}')"
cur.execute(query)
print (f "{cur.rowcount} details inserted" )
conn.commit()
conn.close()
|
Output :
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 :
To insert multiple row in table PRODUCT.
Python3
query = "INSERT INTO PRODUCT (PRODUCT_ID, price,PRODUCT_TYPE) VALUES ('%s', %d, '%s')"
values = [( "1203" , 1000 , "ILL" ),
( "1523" , 1500 , "broadband" ),
( "154" , 14782 , "Voice" ),
]
cur.execute(query,values)
print (f "{cur.rowcount}, details inserted" )
conn.commit()
conn.close()
|
Output :
Note :
- The cursor() is used in order to iterate through the rows.
- Without the command conn.commit() the changes will not be saved.
Last Updated :
14 Oct, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...