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

filter_none

edit
close

play_arrow

link
brightness_4
code

# import the mysql client for python
  
import pymysql
    
# Create a connection object
# IP address of the MySQL database server
Host = "localhost"  
# User name of the database server
User = "user"       
# Password for the database user
Password = ""           
  
database = "database_name"
  
conn  = pymysql.connect(host=Host, user=User, password=Password, database)
  
# Create a cursor object
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()

chevron_right


Output :

inserting rows into table in mariadbinserting rows into table in mariadb

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

filter_none

edit
close

play_arrow

link
brightness_4
code

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()

chevron_right


Output :

inserting multiple rows into table in mariadbinserting multiple rows into table in mariadb

Note :

  • The cursor() is used in order to iterate through the rows.
  • Without the command conn.commit() the changes will not be saved.

Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course.




My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

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.


Article Tags :

1


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.