{"id":4064,"date":"2024-07-22T12:16:35","date_gmt":"2024-07-22T05:16:35","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4064"},"modified":"2024-07-24T20:22:18","modified_gmt":"2024-07-24T13:22:18","slug":"python-sql-server-insert","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/python-sql-server\/python-sql-server-insert\/","title":{"rendered":"Python SQL Server: Inserting Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to insert data into a table in SQL Server from a Python program.<\/p>\n\n\n\n

This tutorial begins where the connecting to the SQL Server from the Python program tutorial<\/a> left off.<\/p>\n\n\n\n

We’ll add a new row to the Authors<\/code> table from a Python program.<\/p>\n\n\n\n

Inserting data into a table #<\/a><\/h2>\n\n\n\n

Step 1. Create a new file insert.py<\/code> file within the project directory.<\/p>\n\n\n\n

Step 2. Define a function insert_author<\/code> that inserts a new author into the Authors<\/code> table:<\/p>\n\n\n

import<\/span> logging\r\nfrom<\/span> connect import<\/span> create_connection\r\n\r\n\r\ndef<\/span> insert_author<\/span>(first_name: str, last_name:str, birth_date:str)<\/span> -> int | None<\/span>:<\/span>\r\n    # Connect to the SQL Sever<\/span>\r\n    conn = create_connection()\r\n    if<\/span> conn is<\/span> None<\/span>:\r\n        return<\/span> None<\/span>\r\n    \r\n    # Insert a new author<\/span>\r\n    with<\/span> (conn, conn.cursor() as<\/span> cursor):\r\n        cursor.execute(\r\n            \"INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES (%s, %s, %s)\"<\/span>,\r\n            (first_name, last_name, birth_date),\r\n        )\r\n        conn.commit()\r\n        logging.info(f'Author: {first_name}<\/span> {last_name}<\/span> inserted successfully.'<\/span>)\r\n        return<\/span> cursor.lastrowid\n\r\n<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the built-in logging<\/code> module and the create_connection<\/code> from the connect<\/code> module.<\/p>\n\n\n
import<\/span> logging\nfrom<\/span> connect import<\/span> create_connection<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, connect to the SQL Server using the create_connection()<\/code> function:<\/p>\n\n\n
conn = create_connection()\nif<\/span> conn is<\/span> None<\/span>:\n   return<\/span> None<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
If the connection fails, return None<\/code> immediately.<\/p>\n\n\n\n
Third, use the Connection<\/code> and Cursor<\/code> objects in the with<\/code> statement to ensure that they are properly managed.:<\/p>\n\n\n
with<\/span> (conn, conn.cursor() as<\/span> cursor):<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, execute an INSERT<\/code> statement with parameterized values to prevent SQL injection:<\/p>\n\n\n
cursor.execute(\n    \"INSERT INTO Authors (FirstName, LastName, BirthDate) VALUES (%s, %s, %s)\"<\/span>,\n     (first_name, last_name, birth_date),\n)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, call the commit()<\/code> method to save the changes to the database.<\/p>\n\n\n
conn.commit()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, log a success message indicating the author was inserted:<\/p>\n\n\n
logging.info(f'Author: {first_name}<\/span> {last_name}<\/span> inserted successfully.'<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, return the ID of the newly inserted row using the cursor.lastrowid<\/code> property:<\/p>\n\n\n
return<\/span> cursor.lastrowid<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Modify the main.py to call the insert_author() function:<\/p>\n\n\n
import logging, sys\r\nfrom insert import insert_author\r\n\r\n# config logging to console<\/span>\r\nlogging.basicConfig(\r\n    stream=sys.stdout, \r\n    encoding='utf-8'<\/span>, \r\n    format='%(levelname)s:%(message)s'<\/span>,\r\n    level=logging.DEBUG\r\n)\r\n\r\n# insert a new author<\/span>\r\nid = insert_author('Alice'<\/span>, 'Johnson'<\/span>, '1978-05-14'<\/span>);\r\nif<\/span> id is not None:\r\n    logging.info(f'Author ID: {id}'<\/span>)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, import the insert_author function from the insert.py module:<\/p>\n\n\n
from<\/span> insert import<\/span> insert_author<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, call the insert_author()<\/code> function to insert a new row into the Authors<\/code> table:<\/p>\n\n\n
id = insert_author('Alice'<\/span>, 'Johnson'<\/span>, '1978-05-14'<\/span>);<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, display the author ID if the row was inserted successfully:<\/p>\n\n\n
if<\/span> id is not None:\n    logging.info(f'Author ID: {id}'<\/span>)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Open your terminal and run the following command:<\/p>\n\n\n
python main.py<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
If you see the following output, you have successfully inserted a new author into the database.<\/p>\n\n\n
INFO:Author: Alice Johnson inserted successfully.\nINFO:Author ID: 1<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Download the project source code #<\/a><\/h2>\n\n\n\n
Download the project source code<\/a><\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Call the execute()<\/code> method of the Cursor object to run an INSERT<\/code> statement to insert a new row into a table.<\/li>\n\n\n\n
  • Use parameterized values in the INSERT<\/code> statement to prevent SQL injection.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t