How to load csv into an empty SQL table, using python?

Question

So, I have this empty table which I created (see code below) and I need to load it with data from a csv file, using python-sql connection. As I do this, need to replace the html codes and change to correct datatypes (clean the file) and finally load it into this empty sql table.

This is the code I wrote but, without any success...when I check the table in SQL it just returns an empty table:

Python code:

import csv
with open ('UFOGB_Observations.csv', 'r') as UFO_Obsr:
    ## Write to the csv file, to clean it and change the html codes:
    with open ('UFO_Observations.csv', 'w') as UFO_Obsw:
      
        for line in UFO_Obsr:
            line = line.replace('&#44', ',') 
            line = line.replace('&#39', "'")
            line = line.replace('&#33', '!')
            line = line.replace('&amp;', '&')
            UFO_Obsw.write(line) 

##To Connect Python to SQL:

import pyodbc
print('Connecting...')
conn = pyodbc.connect('Trusted_Connection=yes', driver = '{ODBC Driver 13 for SQL Server}', server = '.\SQLEXPRESS', database = 'QA_DATA_ANALYSIS')
print('Connected')
cursor = conn.cursor()
print('cursor established')
cursor.execute('''DROP TABLE IF EXISTS UFO_GB_1;
CREATE TABLE UFO_GB_1 (Index_No VARCHAR(10) NOT NULL, date_time VARCHAR(15) NULL, city_or_state VARCHAR(50) NULL, 
country_code VARCHAR(50) NULL, shape VARCHAR (200) NULL, duration VARCHAR(50) NULL, 
date_posted VARCHAR(15) NULL, comments VARCHAR(700) NULL);
''')
print('Commands succesfully completed')

#To insert that csv into the table:

cursor.execute('''BULK INSERT QA_DATA_ANALYSIS.dbo.UFO_GB_1
FROM 'F:\GSS\QA_DATA_ANALYSIS_LEVEL_4\MODULE_2\Challenge_2\TASK_2\UFO_Observations.csv'
WITH ( fieldterminator = '', rowterminator = '\n')''')
    
conn.commit()
conn.close()

I was expecting to see a table with all 1900+ rows, when I type SELECT * FROM table, with correct data types (i.e. date_time and date_posted columns as timestamp)

Griffin · Accepted Answer · 2019-08-01 15:20:22Z

0

(Apologies in advance. New here so not allowed to comment.)

1) Why are you creating the table each time? Is this meant to be a temporary table?

2) What do you get as a response to your query?

3) What happens when you break the task down into parts? Does the code create the table? If the table already exists and you run just the insert data code does it work? When you import the csv and then write back to the same file does that produce the result you are looking for or crash? What if you wrote to a different file and imported that?

answered Aug 1, 2019 at 15:20

Griffin

263 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vanessa_C Over a year ago

Hi! 1) No, the code 'with open....etc.' is just to create the csv file...that part of the code works fine actually. The code to create the table (the SQL stuff) is just to create the empty table, this also works ok...BUT loading the csv into that empty table is the part I'm having problems with because 2) It does not return any rows at all... 3) I've tried every possible way I can think of and Im getting countless errors...I have no idea what they mean since I'm new to coding, really : (

ryati Over a year ago

post the errors. It can help understand what the problem is.

Vanessa_C Over a year ago

Hi! I've had to post an "answer" because it wouldn't let me post the errors in the comments (the character count was too large)

ryati · Accepted Answer · 2019-08-01 22:17:31Z

0

you are writing your queries like how you would in SQL, but you need to re-write them in python. Python needs to understand the query as a python string, then it can parse it into sql. IE don't wrap the statement with '''.

This is not tested, but try something like this:

bulk_load_sql = """
BULK INSERT QA_DATA_ANALYSIS.dbo.UFO_GB_1
FROM 'F:\GSS\QA_DATA_ANALYSIS_LEVEL_4\MODULE_2\Challenge_2\TASK_2\UFO_Observations.csv'
WITH ( fieldterminator = '', rowterminator = '\n')
"""

cursor.execute(bulk_load_sql)

This uses a docstring to put the sql on multiple lines, but you may want to use a regular string.

Here is an answer that goes over formatting your query for pyodbc https://stackoverflow.com/a/43855693/4788717

edited Aug 1, 2019 at 22:17

answered Aug 1, 2019 at 15:24

ryati

3603 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to load csv into an empty SQL table, using python?

Python code:

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Python code:

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related