Inserting rows from csv into mysql database

Question

I'm extracting all csv in a given folder and trying to insert them into a mysql database row by row. Here is my code:

def upload_to_db(table, folder):

print('Uploading...', end='')

files = grab_files(folder)

# Connect to dfeventlogger database
connection = pymysql.connect(**eventlogger_config, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:

        # Open each csv in the files list
        # and ignore column headers
        for file in files:
            csv_file = open_csv(file)
            csv_headers = csv_file[0]
            #csv_headers = tuple(csv_file[0]) #tried this too with no luck
            csv_data = csv_file[1:]

            # Insert each row of each csv into eventlogger table
            for row in csv_data:
                placeholders = ', '.join(['%s'] * len(row))
                sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (table, csv_headers, placeholders)
                print(csv_headers, '\n')
                print(sql, '\n')
                print(row)
                cursor.execute(sql, row)
    # Connection is not autocommit by default.
    # So you must commit to save your changes.
    connection.commit()

finally:
    connection.close()

print('Finished')

upload_to_db('gsSearchAnalyticsTest', 'some_folder')

Here is the output:

Uploading...['date', 'site_id', 'site', 'landing_page', 'keyword', 'source', 'impressions', 'clicks', 'position'] 

INSERT INTO gsSearchAnalyticsTest ( ['date', 'site_id', 'site', 'landing_page', 'keyword', 'source', 'impressions', 'clicks', 'position'] ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s ) 

['2018-01-01', '2', 'something.co.uk', 'something.co.uk/somewhere_in_the_world', 'somewhere in the world', 'uk', '1', '1', '2.000000000000']

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['date', 'site_id', 'site', 'landing_page', 'keyword', 'source', 'impressions', ' at line 1")

Here's what I'm trying to do:

Extract data from a source database
Export as a csv and save in some folder
Pick up and loop through all csv files in folder and insert into target database

Question is, where have I gone wrong in my code? And how do I rectify it?

kubarik · Accepted Answer · 2019-10-09 14:00:18Z

1

Remove square brackets from the query "INSERT INTO gsSearchAnalyticsTest ( ['date',...]) VALUES (...)"

answered Oct 9, 2019 at 14:00

kubarik

644 bronze badges

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

2 Comments

AK91 Over a year ago

Removed square brackets from placeholders = ', '.join('%s' * len(row)) but got ValueError: unsupported format character ',' (0x2c) at index 150

AK91 Over a year ago

Also tried converting csv_headers to a tuple and removing parentheses from INSERT INTO %s ( %s ) still not executing and getting a ProgrammingError

AK91 · Accepted Answer · 2019-10-10 11:41:13Z

0

It worked when I converted csv_headers = csv_file[0] to csv_headers = ', '.join(csv_file[0])

answered Oct 10, 2019 at 11:41

AK91

7412 gold badges17 silver badges45 bronze badges

Collectives™ on Stack Overflow

Inserting rows from csv into mysql database

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related