{"id":4092,"date":"2024-07-23T09:39:30","date_gmt":"2024-07-23T02:39:30","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4092"},"modified":"2024-07-24T19:43:55","modified_gmt":"2024-07-24T12:43:55","slug":"python-sql-server-bulk-copy","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/python-sql-server\/python-sql-server-bulk-copy\/","title":{"rendered":"Python SQL Server: Bulk Copy"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to quickly insert data into the SQL Server table using the bulk copy function.<\/p>\n\n\n\n

This tutorial begins where\u00a0the\u00a0import data from a CSV file into a SQL Server table tutorial<\/a>\u00a0left off.<\/p>\n\n\n\n

To quickly insert a lot of data into the SQL Server table, you can use the bulk copy function. We’ll demonstrate how to perform a bulk data copy into the Customers<\/code> table.<\/p>\n\n\n\n

Perform a bulk copy of customer data #<\/a><\/h2>\n\n\n\n

Step 1. Download and copy the following customers.csv<\/code> data into the data directory:<\/p>\n\n\n\n

Download the customers.csv file<\/a><\/p>\n\n\n\n

The customers.csv<\/code> file includes 500 customer records, with all the columns from the Customers<\/code> table including CustomerID<\/code>, FirstName<\/code>, LastName<\/code>, Email<\/code>, PhoneNumber<\/code>, and Address<\/code>.<\/p>\n\n\n\n

Step 2. Create a new file bulk.py<\/code> within the project directory:<\/p>\n\n\n

mkdir data<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Add the following code to the bulk.py<\/code> file:<\/p>\n\n\n
from<\/span> connect import<\/span> create_connection\nfrom<\/span> utils import<\/span> read_csv\n\n\ndef<\/span> bulk_copy_customers<\/span>(filename:str)<\/span> -> bool:<\/span>\n    customers = read_csv(filename)\n    if<\/span> not<\/span> customers:\n        return<\/span> False<\/span>\n    \n    conn = create_connection()\n    if<\/span> conn is<\/span> None<\/span>:\n        return<\/span> False<\/span>\n    \n    with<\/span> conn:\n        conn.bulk_copy('Customers'<\/span>,customers)\n        conn.commit()\n\n    return<\/span> True<\/span>   <\/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 create_connection<\/code> function from the connect<\/code> module and read_csv<\/code> function from the utils module:<\/p>\n\n\n
from<\/span> connect import<\/span> create_connection\nfrom<\/span> utils import<\/span> read_csv<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, define a function bulk_copy_customers<\/code> that accepts a CSV file name as a string and returns a boolean value indicating whether the bulk copy succeeds:<\/p>\n\n\n
def<\/span> bulk_copy_customers<\/span>(filename:str)<\/span> -> bool:<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, read the customer data from the CSV file specified by the filename argument, and return False if the customer file is empty:<\/p>\n\n\n
customers = read_csv(filename)\nif<\/span> not<\/span> customers:\n    return<\/span> False<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, create a new connection to the SQL Server by calling the create_connection<\/code> function, and return False if the connection fails:<\/p>\n\n\n
conn = create_connection()\nif<\/span> conn is<\/span> None<\/span>:\n    return<\/span> False<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, call the bulk_copy<\/code> function of the Connection object to perform a bulk copy:<\/p>\n\n\n
with<\/span> conn:\n    conn.bulk_copy('Customers'<\/span>,customers)\n    conn.commit()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
The bulk_copy<\/code> function accepts a table name and a list of tuples, with each tuple containing data representing a customer record.<\/p>\n\n\n\n
The with<\/code> statement ensures that the Connection<\/code> object is managed properly so you don’t have to manually close it.<\/p>\n\n\n\n
The commit()<\/code> method permanently applies changes to the database. If you don’t call the commit()<\/code> method, the changes won’t be saved to the database.<\/p>\n\n\n\n
Finally, return True from the function to indicate that the bulk copy was successful:<\/p>\n\n\n
return<\/span> True<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Modify the main.py<\/code> module that uses the bulk_copy_customers<\/code> function:<\/p>\n\n\n
import<\/span> logging, sys\nfrom<\/span> bulk import<\/span> bulk_copy_customers \n\n# config logging to console<\/span>\nlogging.basicConfig(\n    stream=sys.stdout, \n    encoding='utf-8'<\/span>, \n    format='%(levelname)s:%(message)s'<\/span>,\n    level=logging.DEBUG\n)\n\nbulk_copy_customers('.\/data\/customers.csv'<\/span>)<\/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 logging and sys for logging purposes:<\/p>\n\n\n
import<\/span> logging, sys<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, import the bulk_copy_customers<\/code> function from the bulk<\/code> module:<\/p>\n\n\n
from<\/span> bulk import<\/span> bulk_copy_customers<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, configure the logging to output messages to the terminal:<\/p>\n\n\n
logging.basicConfig(\n    stream=sys.stdout, \n    encoding='utf-8'<\/span>, \n    format='%(levelname)s:%(message)s'<\/span>,\n    level=logging.DEBUG\n)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, call the bulk_copy_customers<\/code> function to copy customer data from the .\/data\/customers.csv<\/code> to the Customers table:<\/p>\n\n\n
bulk_copy_customers('.\/data\/customers.csv'<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 5. Open your terminal and run the main.py<\/code> file:<\/p>\n\n\n
python main.py<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Verifying the bulk copy #<\/a><\/h2>\n\n\n\n
Step 1. Launch SQL Server Management Studio and connect to the SQL Server.<\/p>\n\n\n\n
Step 2. Execute the following query that retrieves data from the Customers<\/code> table:<\/p>\n\n\n
SELECT * FROM customers;<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"python<\/figure>\n\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
  • Use the bulk_copy()<\/code> method of the Connection object to quickly load data into an SQL Server table.<\/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
Here’s more information on the connect<\/a> and utils<\/a> modules.<\/p>\n\n\n\n