CODEWITHSUNDEEP
pythonmysqlpymysql
AK91
AK91

Reputation: 731

Inserting rows from csv into mysql database

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:

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

Upvotes: 0

Views: 759

Answers (2)

AK91
AK91

Reputation: 731

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

Upvotes: 0

kubarik
kubarik

Reputation: 64

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

Upvotes: 1

Related Questions