{"id":4078,"date":"2024-07-22T15:59:26","date_gmt":"2024-07-22T08:59:26","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4078"},"modified":"2024-07-24T20:26:17","modified_gmt":"2024-07-24T13:26:17","slug":"python-sql-server-import-from-csv","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/python-sql-server\/python-sql-server-import-from-csv\/","title":{"rendered":"Python SQL Server: Import from CSV"},"content":{"rendered":"\n

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

This tutorial begins where\u00a0the\u00a0insert data into an SQL Server table from a Python program tutorial<\/a>\u00a0left off.<\/p>\n\n\n\n

Preparing a CSV file #<\/a><\/h2>\n\n\n\n

Step 1. Create a new directory in the project directory called data<\/code>.<\/p>\n\n\n\n

Step 2. Copy the following authors.csv<\/code> file that contains author data for the data<\/code> directory:<\/p>\n\n\n\n

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

The authors.csv<\/code> file has three columns including first name, last name, and birth date:<\/p>\n\n\n

First Name,Last Name,Birth Date\nRobin,Calderon,1977-06-12\nDavid,Hunter,1983-08-01\nCraig,Hart,1991-12-13\n...<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Reading data from a CSV file #<\/a><\/h2>\n\n\n\n
Step 1. Create a new module called utils.py<\/code>.<\/p>\n\n\n\n
Step 2. Define a function read_csv<\/code> that reads data from a CSV file specified by a file name and returns a tuple:<\/p>\n\n\n
import<\/span> csv\n        \ndef<\/span> read_csv<\/span>(filename:str)<\/span> -> tuple:<\/span>\n    with<\/span> open(filename, 'r'<\/span>) as<\/span> f:\n        reader = csv.reader(f)\n        next(reader)  # skip the header<\/span>\n        return<\/span> [tuple(row) for<\/span> row in<\/span> reader]<\/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, open the CSV file:<\/p>\n\n\n
with<\/span> open(filename, 'r'<\/span>) as<\/span> f:<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, the CSV file line by line: <\/p>\n\n\n
reader = csv.reader(f)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, skip the header:<\/p>\n\n\n
next(reader)  # skip the header<\/span><\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, convert each line into a tuple before returning all the lines as a list of tuples.<\/p>\n\n\n
return<\/span> [tuple(row) for<\/span> row in<\/span> reader]<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Import data into a table #<\/a><\/h2>\n\n\n\n
Step 1. Create a new module import_author.py<\/code> within the project directory:<\/p>\n\n\n\n
Step 2. Define import_author_from_csv<\/code> function to import data from a CSV file and insert each line into the Authors<\/code> table:<\/p>\n\n\n
from<\/span> insert import<\/span> insert_author\nfrom<\/span> utils import<\/span> read_csv\n\ndef<\/span> import_author_from_csv<\/span>(filename: str)<\/span> -> None<\/span>:<\/span>\n    authors = read_csv(filename)\n    for<\/span> author in<\/span> authors:\n        insert_author(*author)<\/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 insert function from the insert_author<\/code> module and read_csv<\/code> function from the utils<\/code> module:<\/p>\n\n\n
from<\/span> insert import<\/span> insert_author\nfrom<\/span> utils import<\/span> read_csv<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, read data from the CSV file specified by the filename using the read_csv<\/code> function:<\/p>\n\n\n
authors = read_csv(filename)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
The read_csv<\/code> function returns a list of tuples, each containing the author’s first name, last name, and birth date.<\/p>\n\n\n\n
Third, insert each author into the Authors<\/code> table by calling the insert_author<\/code> function:<\/p>\n\n\n
for<\/span> author in<\/span> authors:\n    insert_author(*author)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Modify the main.py<\/code> file to use the import_author_from_csv<\/code> function to import data from the .\/data\/authors.csv<\/code> into the Authors<\/code> table:<\/p>\n\n\n
import<\/span> logging, sys\nfrom<\/span> import_author import<\/span> import_author_from_csv \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\n# import data from csv file<\/span>\nimport_author_from_csv('.\/data\/authors.csv'<\/span>)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Open your terminal and execute 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
Step 5. Verify the import<\/p>\n\n\n\n
Open the SQL Server Management Studio (SSMS), connect to the SQL Server, and execute the following query:<\/p>\n\n\n
SELECT * FROM Authors;<\/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 csv<\/code> module to read data from a CSV file<\/li>\n\n\n\n
  • Execute the INSERT<\/code> statement to insert each line of the CSV file into a 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