In this article, we\u2019ll learn how to insert multiple entries or data in the database using a single SQL query in Python.<\/p>\n\n\n\n
We\u2019ll see how we can insert multiple entries in the two different databases SQLite<\/strong> and MySQL<\/strong>.<\/p>\n\n\n\n Create a Python file and write the following code inside it.<\/p>\n\n\n\n This code imports the We created and connected ( Using the cursor, we executed an SQL query to create a table named Then, we have a list of tuples containing the car information stored in the Now if you look closely, we used executemany() that will run for every parameter (entries). This single line is executed five times for five different entries.<\/p>\n\n\n\n We used question mark (?) in the SQL query as a placeholder for the values. In SQLite, question mark (?) is used as a placeholder but for MySQL or PostgreSQL, this will be different.<\/p>\n<\/blockquote>\n\n\n\n Next, the changes were saved using Another SQL query was executed to select all of the data from the table, which was then retrieved using Finally, the database connection was closed using Create a Python file and write the following code.<\/p>\n\n\n\n In this code, we\u2019ve used the PyMySQL<\/strong> database driver to connect with the MySQL database in Python.<\/p>\n\n\n\n We performed all the preliminary operations such as connecting to MySQL server and creating a cursor object using PyMySQL.<\/p>\n\n\n\n Then, we created a table named Next, we stored a list of tuples containing relevant data for the table in the Then we used In MySQL, we use (%s) as a placeholder for a value.<\/p>\n<\/blockquote>\n\n\n\n Next, the changes were saved using Then, we selected all rows in the database table We got the results in a dictionary format because we set the cursor class to That\u2019s all for now.<\/strong><\/p>\n\n\n\n Keep Coding\u270c\u270c.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":" In this article, we\u2019ll learn how to insert multiple entries or data in the database using a single SQL query in Python. Insert Multiple Entries in the Database We\u2019ll see how we can insert multiple entries in the two different databases SQLite and MySQL. SQLite Create a Python file and write the following code inside […]<\/p>\n","protected":false},"author":1,"featured_media":1790,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ocean_post_layout":"","ocean_both_sidebars_style":"","ocean_both_sidebars_content_width":0,"ocean_both_sidebars_sidebars_width":0,"ocean_sidebar":"","ocean_second_sidebar":"","ocean_disable_margins":"enable","ocean_add_body_class":"","ocean_shortcode_before_top_bar":"","ocean_shortcode_after_top_bar":"","ocean_shortcode_before_header":"","ocean_shortcode_after_header":"","ocean_has_shortcode":"","ocean_shortcode_after_title":"","ocean_shortcode_before_footer_widgets":"","ocean_shortcode_after_footer_widgets":"","ocean_shortcode_before_footer_bottom":"","ocean_shortcode_after_footer_bottom":"","ocean_display_top_bar":"default","ocean_display_header":"default","ocean_header_style":"","ocean_center_header_left_menu":"","ocean_custom_header_template":"","ocean_custom_logo":0,"ocean_custom_retina_logo":0,"ocean_custom_logo_max_width":0,"ocean_custom_logo_tablet_max_width":0,"ocean_custom_logo_mobile_max_width":0,"ocean_custom_logo_max_height":0,"ocean_custom_logo_tablet_max_height":0,"ocean_custom_logo_mobile_max_height":0,"ocean_header_custom_menu":"","ocean_menu_typo_font_family":"","ocean_menu_typo_font_subset":"","ocean_menu_typo_font_size":0,"ocean_menu_typo_font_size_tablet":0,"ocean_menu_typo_font_size_mobile":0,"ocean_menu_typo_font_size_unit":"px","ocean_menu_typo_font_weight":"","ocean_menu_typo_font_weight_tablet":"","ocean_menu_typo_font_weight_mobile":"","ocean_menu_typo_transform":"","ocean_menu_typo_transform_tablet":"","ocean_menu_typo_transform_mobile":"","ocean_menu_typo_line_height":0,"ocean_menu_typo_line_height_tablet":0,"ocean_menu_typo_line_height_mobile":0,"ocean_menu_typo_line_height_unit":"","ocean_menu_typo_spacing":0,"ocean_menu_typo_spacing_tablet":0,"ocean_menu_typo_spacing_mobile":0,"ocean_menu_typo_spacing_unit":"","ocean_menu_link_color":"","ocean_menu_link_color_hover":"","ocean_menu_link_color_active":"","ocean_menu_link_background":"","ocean_menu_link_hover_background":"","ocean_menu_link_active_background":"","ocean_menu_social_links_bg":"","ocean_menu_social_hover_links_bg":"","ocean_menu_social_links_color":"","ocean_menu_social_hover_links_color":"","ocean_disable_title":"default","ocean_disable_heading":"default","ocean_post_title":"","ocean_post_subheading":"","ocean_post_title_style":"","ocean_post_title_background_color":"","ocean_post_title_background":0,"ocean_post_title_bg_image_position":"","ocean_post_title_bg_image_attachment":"","ocean_post_title_bg_image_repeat":"","ocean_post_title_bg_image_size":"","ocean_post_title_height":0,"ocean_post_title_bg_overlay":0.5,"ocean_post_title_bg_overlay_color":"","ocean_disable_breadcrumbs":"default","ocean_breadcrumbs_color":"","ocean_breadcrumbs_separator_color":"","ocean_breadcrumbs_links_color":"","ocean_breadcrumbs_links_hover_color":"","ocean_display_footer_widgets":"default","ocean_display_footer_bottom":"default","ocean_custom_footer_template":"","ocean_post_oembed":"","ocean_post_self_hosted_media":"","ocean_post_video_embed":"","ocean_link_format":"","ocean_link_format_target":"self","ocean_quote_format":"","ocean_quote_format_link":"post","ocean_gallery_link_images":"on","ocean_gallery_id":[],"footnotes":""},"categories":[37,2],"tags":[38,31],"class_list":["post-1789","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","category-python","tag-database","tag-python3","entry","has-media"],"_links":{"self":[{"href":"https:\/\/geekpython.in\/wp-json\/wp\/v2\/posts\/1789","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/geekpython.in\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/geekpython.in\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/geekpython.in\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/geekpython.in\/wp-json\/wp\/v2\/comments?post=1789"}],"version-history":[{"count":3,"href":"https:\/\/geekpython.in\/wp-json\/wp\/v2\/posts\/1789\/revisions"}],"predecessor-version":[{"id":1793,"href":"https:\/\/geekpython.in\/wp-json\/wp\/v2\/posts\/1789\/revisions\/1793"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/geekpython.in\/wp-json\/wp\/v2\/media\/1790"}],"wp:attachment":[{"href":"https:\/\/geekpython.in\/wp-json\/wp\/v2\/media?parent=1789"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/geekpython.in\/wp-json\/wp\/v2\/categories?post=1789"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/geekpython.in\/wp-json\/wp\/v2\/tags?post=1789"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}SQLite<\/h1>\n\n\n\n
import sqlite3\n\n# Create and connect with database\nconnection = sqlite3.connect(\"cars.db\")\n\n# Create cursor to interact with database\ncursor = connection.cursor()\n\n# Create database table\ncursor.execute(\n \"\"\"CREATE TABLE IF NOT EXISTS car (brand text, model text)\"\"\"\n)\n\nentries = [\n ('Ferrari', '812 Superfast'),\n ('Porsche', 'Carrera GT'),\n ('BMW', 'i8'),\n ('BMW', 'X7'),\n ('Ferrari', '488 GTB')\n]\n\n# Insert entries in the database table\ncursor.executemany(\n \"\"\"INSERT INTO car (brand, model) VALUES (?, ?)\"\"\", entries\n)\n\n# Commit the changes\nconnection.commit()\n\n# Fetch the result from the database table\nresult = cursor.execute(\n \"\"\"SELECT * FROM car\"\"\"\n)\n\n# Print the entries in the table\ndata = result.fetchall()\nfor each in data:\n print(each)\n\n# Close the database connection\nconnection.close()<\/pre><\/div>\n\n\n\nsqlite3<\/code> library that helps create and interact with the SQLite database.<\/p>\n\n\n\nsqlite3.connect(\"cars.db\")<\/code>) to the cars.db<\/code> database and then created a cursor (connection.cursor()<\/code>) to interact with the database.<\/p>\n\n\n\ncar<\/code> with two columns: brand<\/code> and model<\/code> in the database.<\/p>\n\n\n\nentries<\/code> variable.<\/p>\n\n\n\n\n
connection.commit()<\/code>.<\/p>\n\n\n\nfetchall()<\/code> and printed.<\/p>\n\n\n\nconnection.close()<\/code>.<\/p>\n\n\n\n('Ferrari', '812 Superfast')\n('Porsche', 'Carrera GT')\n('BMW', 'i8')\n('BMW', 'X7')\n('Ferrari', '488 GTB')<\/pre><\/div>\n\n\n\nMySQL<\/h1>\n\n\n\n
import pymysql\n\n# Establish MySQL connection with Python\nsql_connection = pymysql.connect(\n host=\"localhost\",\n user=\"root\",\n password=\"xxxxxx\",\n database='cars',\n cursorclass=pymysql.cursors.DictCursor\n)\n\n# Create cursor to interact with database\ncursor = sql_connection.cursor()\n\n# Create database table\ncursor.execute(\n \"\"\"CREATE TABLE IF NOT EXISTS car (brand text, model text)\"\"\"\n)\n\n# Entries to be made\nentries = [\n ('Ferrari', '812 Superfast'),\n ('Porsche', 'Carrera GT'),\n ('BMW', 'i8'),\n ('BMW', 'X7'),\n ('Ferrari', '488 GTB')\n]\n\n# Insert entries in the database table\ncursor.executemany(\n \"\"\"INSERT INTO car (brand, model) VALUES (%s, %s)\"\"\", entries\n)\n\n# Save the changes in the database\nsql_connection.commit()\n\n# Fetch data from the database table\ncursor.execute(\n \"\"\"SELECT * FROM car\"\"\"\n)\n\ndata = cursor.fetchall()\nfor each in data:\n print(each)\n\n# Close the connection with database\nsql_connection.close()<\/pre><\/div>\n\n\n\ncar<\/code> with two columns within the cars<\/code> database by executing an SQL query.<\/p>\n\n\n\nentries<\/code> variable.<\/p>\n\n\n\nexecutemany()<\/code> to execute a single query repeatedly for each entry. This operation will insert every entry in the table stored in the entries variable.<\/p>\n\n\n\n\n
connection.commit()<\/code>.<\/p>\n\n\n\ncar<\/code> by executing an SQL query. The selected rows were then fetched using fetchall()<\/code> and printed.<\/p>\n\n\n\n{'brand': 'Ferrari', 'model': '812 Superfast'}\n{'brand': 'Porsche', 'model': 'Carrera GT'}\n{'brand': 'BMW', 'model': 'i8'}\n{'brand': 'BMW', 'model': 'X7'}\n{'brand': 'Ferrari', 'model': '488 GTB'}<\/pre><\/div>\n\n\n\nDictCursor<\/code>.<\/p>\n\n\n\n
\n\n\n\n