CSV data causes error when uploading to SQL table via Python
<p>So I've been struggling with this problem for the last couple of days. I need to upload a CSV file with about 25 columns &amp; 50K rows into a SQL Server table (<code>zzzOracle_Extract</code>) which also contains 25 columns, same Column names &amp; in the same order.</p>

<p>This is what a row looks like from the CSV file:</p>

<pre><code>['M&amp;M OPTICAL SHOP', '3001211', 'SHORE', '*', 'PO BOX 7891', '', '', '', 'GUAYNABO', 'GUAYNABO', 'PR', '0090', 'United States', '24-NSH RETAIL CUSTOMER', 'SH02-SHORE COLUMN 2', '3001211', '*', '*', '*', '3001211744-BILL_TO', '', '', '', '', 'RACHAEL']
</code></pre>

<p>So in total, there are 25 columns with some values being blank. Maybe this is causing an error. Here is my code:</p>

<pre><code>import csv
import pymssql

conn = pymssql.connect(
    server="xxxxxxxxxx",
    port = 2433,
    user='SQLAdmin',
    password='xxxxx',
    database='NasrWeb'
)

with open('cleanNVG.csv','r') as f:
    reader = csv.reader(f)
    columns = next(reader)
    query = 'insert into dbo.zzzOracle_Extract({0}) Values({1})'
    query = query.format(','.join(columns),','.join('?' * len(columns)))
    cursor = conn.cursor()
    for data in reader:
        print(data) #What a row looks like
        cursor.execute(query,data)
    cursor.commit()

cursor.close()
print("Done")
conn.close()
</code></pre>

<p>After the script is executed, one of the errors I get is the following:</p>

<pre><code>ValueError: 'params' arg (&lt;class 'list'&gt;) can be only a tuple or a dictionary.
</code></pre>

<p>What can be wrong with my code? I really appreciate the help!</p>

<blockquote>
  <p>How do join the [ ] to each column in my code?</p>
</blockquote>

<p>So you have something like </p>



<pre class="lang-python prettyprint-override"><code>&gt;&gt;&gt; columns = ['ID','Last Name','First Name']
</code></pre>

<p>and you're currently using </p>

<pre class="lang-python prettyprint-override"><code>&gt;&gt;&gt; ','.join(columns)
'ID,Last Name,First Name'
</code></pre>

<p>but now you need to wrap the column names in square brackets. That could be done with</p>

<pre class="lang-python prettyprint-override"><code>&gt;&gt;&gt; ','.join('[' + x + ']' for x in columns)
'[ID],[Last Name],[First Name]'
</code></pre>

python sql-server python-3.x pymssql