title: How to use Bulk insert to insert data from Dataframe to SQL Server table?
body: <p>I'm new to Python so reaching out for help. I have a csv file in S3 bucket, I would like to use Python pyodbc to import this csv file to a table in SQL server. This file is 50 MB (400k records). My code is below. As my code states below, my csv data is in a dataframe, how can I use Bulk insert to insert dataframe data into sql server table. If my approach does not work, please advise me with a different approach.</p>
  <pre><code># Connection to S3
  s3 = boto3.client(
      service_name = 's3',
      region_name = 'us-gov-west-1',
      aws_access_key_id = 'ZZZZZZZZZZZZZZZZZZ',
      aws_secret_access_key = 'AAAAAAAAAAAAAAAAA')
  
  # Connection to SQL Server
  server = 'myserver.amazonaws.com'
  path = 'folder1/folder2/folder3/myCSVFile.csv'
  
  cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE=DB-staging;UID=User132;PWD=XXXXXX')
      cursor = cnxn.cursor()
      
  obj_sum = s3.get_object(Bucket = 'my_bucket', Key = path)
  csv_data = pd.read_csv(obj_sum['Body'])
  df = pd.DataFrame(csv_data, columns = ['SYSTEM_NAME', 'BUCKET_NAME', 'LOCATION', 'FILE_NAME', 'LAST_MOD_DATE', 'FILE_SIZE'])
  #print(df.head(n=15).to_string(index=False))
  
  # Insert DataFrame to table
  cursor.execute(&quot;&quot;&quot;truncate table dbo.table1&quot;&quot;&quot;)
  cursor.execute(&quot;&quot;&quot;BULK INSERT dbo.table1 FROM &quot;&quot;&quot; + .....# what do I put here since data is in dataframe??)
  </code></pre>
  <p>I tried to loop through the dataframe and it took 20 minutes to insert 5k records. Code below. Looping through each record is an option but a poor one. This is why I'm moving towards bulk insert if possible.</p>
  <pre><code>for i in df.itertuples(index = False):
      if i.FILE_SIZE != 0:
         cursor.execute(&quot;&quot;&quot;insert into dbo.table1 (SYSTEM_NAME, BUCKET_NAME, X_LOCATION, FILE_NAME, LAST_MOD_DATE, FILE_SIZE) 
                    values (?,?,?,?,?,?)&quot;&quot;&quot;, i.SYSTEM_NAME, i.BUCKET_NAME, i.LOCATION, i.FILE_NAME, i.LAST_MOD_DATE, i.FILE_SIZE)
  </code></pre>
  <p>Lastly, bonus question ... I would like to check if the &quot;FILE_SIZE&quot; column in my dataframe equals to 0, if it is skip over that record and move forward to the next record.</p>
  <p>Thank you in advnace.</p>