Writing a csv file into SQL Server database using python

I modified the code written above by Brian as follows since the one posted above wouldn't work on the delimited files that I was trying to upload. The line row.pop() can also be ignored as it was necessary only for the set of files that I was trying to upload.

import csv

def upload_table(path, filename, delim, cursor):
    """
    Function to upload flat file to sqlserver
    """
    tbl = filename.split('.')[0]
    cnt = 0
    with open (path + filename, 'r') as f:
        reader = csv.reader(f, delimiter=delim)
        for row in reader:
            row.pop() # can be commented out
            row = ['NULL' if val == '' else val for val in row]
            row = [x.replace("'", "''") for x in row]
            out = "'" + "', '".join(str(item) for item in row) + "'"
            out = out.replace("'NULL'", 'NULL')
            query = "INSERT INTO " + tbl + " VALUES (" + out + ")"
            cursor.execute(query)
            cnt = cnt + 1
            if cnt % 10000 == 0:
                cursor.commit()
        cursor.commit()
    print("Uploaded " + str(cnt) + " rows into table " + tbl + ".")

I got it sorted out. The error was due to the size restriction restriction of table. It changed the column capacity like from col1 varchar(10) to col1 varchar(35) etc. Now it's working fine.

You can pass the columns as arguments. For example:

for rows in csv_data: # Iterate through csv
    cur.execute("INSERT INTO MyTable(Col1,Col2,Col3,Col4) VALUES (?,?,?,?)", *rows)

If you are using MySqlHook in airflow , if cursor.execute() with params throw san error

TypeError: not all arguments converted during string formatting

use %s instead of ?

with open('/usr/local/airflow/files/ifsc_details.csv','r') as csv_file:
    csv_reader = csv.reader(csv_file)
    columns = next(csv_reader)
    query = '''insert into ifsc_details({0}) values({1});'''
    query = query.format(','.join(columns), ','.join(['%s'] * len(columns)))
    mysql = MySqlHook(mysql_conn_id='local_mysql')
    conn = mysql.get_conn()
    cursor = conn.cursor()
    for data in csv_reader:
        cursor.execute(query, data)
    cursor.commit()

Here is the script and hope this works for you:

import pandas as pd    
import pyodbc as pc    
connection_string = "Driver=SQL Server;Server=localhost;Database={0};Trusted_Connection=Yes;"                           
cnxn = pc.connect(connection_string.format("DataBaseNameHere"), autocommit=True)    
cur=cnxn.cursor()
df= pd.read_csv("your_filepath_and_filename_here.csv").fillna('')    
query = 'insert into TableName({0}) values ({1})'    
query = query.format(','.join(df.columns), ','.join('?' * len(df1.columns)))        
cur.fast_executemany = True    
cur.executemany(query, df.values.tolist())
cnxn.close()

For my purposes, I was adding data to an existing table, and needed to be able to remove unnecessary columns. This is the code that worked for me:

# Read csv file and store data in the table
df = pd.read_csv('C:\\Users\\Admin\\Desktop\\data\\daily\\mx\\' + ticker_name + '.MX.csv')
df.columns =  ['date', 'open', 'high', 'low', 'close', 'adj_close', 'vol']
df = df.drop(['adj_close'], axis=1)  

records = df.to_records(index=False)
records = list(records)
cursor = connection.cursor()
query = "INSERT INTO " + table_name + " (date, open, high, low, close, volume) VALUES (%s, %s, %s, %s, %s, %s)"
cursor.executemany(query, records)
connection.commit()
connection.close()

You can also import data into SQL by using either:

• The SQL Server Import and Export Wizard
• SQL Server Integration Services (SSIS)
• The OPENROWSET function

More details can be found on this webpage: https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017