1

So I'm trying to get a CSV file into an SQL Server,

sample csv file

STUFF,NAME,A DATE,A TIME,ANOTHER DATE,ANOTHER TIME,A NUMBER Bananas
John Smith,2019-11-20,17:00,2019-11-20,20:00,3 Apples,Jane Doe,2019-11-07,17:00,2019-11-07,23:00,6

here's how I'm trying to do it (based on this):

import csv  
import pyodbc  

thecsv = 'iamacsvfile.csv'

print('connecting')
drivr = "SQL Server"
servr = "1.2.3.4"
db = "testdata"
username = "user"
password = "thepassword"
my_cnxn = pyodbc.connect('DRIVER={};SERVER={};DATABASE={};UID={};PWD={}'.format(drivr,servr,db,username,password))

my_cursor = my_cnxn.cursor()

def insert_records(table, thecsv, my_cursor, my_cnxn):

    with open(thecsv) as csvfile:
        csvFile = csv.reader(csvfile, delimiter=',')
        header = next(csvFile)
        headers = map((lambda x: x.strip()), header)
        insert = 'INSERT INTO {} ('.format(table) + ', '.join(headers) + ') VALUES ({})' .format(', '.join(len(headers) * '?'))
        for row in csvFile:
            values = map((lambda x: x.strip()), row)  
            my_cursor.execute(insert, values) 
            my_cnxn.commit()


table = 'dbo.iamthetable'
mycsv = thecsv 
insert_records(table, mycsv, my_cursor, my_cnxn)
my_cursor.close()

Error message:

insert = 'INSERT INTO {} ('.format(table) + ', '.join(headers) + ') VALUES ({})' .format(', '.join(len(headers) * '?'))
TypeError: object of type 'map' has no len()

I've seen some similar examples of this type of error (such as here) but I'm not sure how the solutions apply to this particular usage case. Can anyone assist?

(by the way if this entire code block is bad I'm open to a completely different method of doing the same, but haven't found anything that works yet)

Dale K
  • 25,246
  • 15
  • 42
  • 71
츄 plus
  • 488
  • 1
  • 7
  • 20
  • i think it's better you put sample csv file as well, it's helpful. Also better you can put `print(values)` inside the for loop to check what's going on. – Sha Dec 04 '19 at 23:08
  • `STUFF,NAME,A DATE,A TIME,ANOTHER DATE,ANOTHER TIME,A NUMBER Bananas,John Smith,2019-11-20,17:00,2019-11-20,20:00,3 Apples,Jane Doe,2019-11-07,17:00,2019-11-07,23:00,6` etc – 츄 plus Dec 04 '19 at 23:12
  • 1
    Try `list(map((lambda x: x.strip()), header))` instead of `map((lambda x: x.strip()), header)`. Basically everytime you get a `map`, convert to list since map has no len method. – fizzybear Dec 04 '19 at 23:24
  • @Sha `print(values)` inside the for loop is a great idea but is not working as the program doesn't get that far. – 츄 plus Dec 04 '19 at 23:28
  • @fizzybear okay a new error: `my_cursor.execute(insert, values) pyodbc.ProgrammingError: ('The SQL contains 7 parameter markers, but 1 parameters were supplied', 'HY000')` – 츄 plus Dec 04 '19 at 23:32
  • have a look on [pyodbc/issues](https://github.com/mkleehammer/pyodbc/issues/552) – Sha Dec 04 '19 at 23:40
  • @Sha I don't see how that applies. I thought that `insert = 'INSERT INTO {} ('.format(table) + ', '.join(headers) + ') VALUES ({})' .format(', '.join(len(headers) * '?'))` dynamically covers this? I mean, obviously I'm wrong, but adding their params line didn't work for me. I don't see how it relates. – 츄 plus Dec 04 '19 at 23:48
  • @fizzybear In which case there is practically no reason to use `map()`, no? – AMC Dec 05 '19 at 04:10

1 Answers1

1

Both of your issues are caused by the fact that with modern versions of Python (i.e., Python_3), map() returns a map object which is an iterable, not a list. Consequently,

import csv

with open('C:/Users/Gord/Desktop/foo.csv', 'r') as csvfile:
    csvFile = csv.reader(csvfile, delimiter=',')
    header = next(csvFile)
    print(type(header))  # <class 'list'>
    print(len(header))  # 3
    headers_map = map((lambda x: x.strip()), header)
    print(type(headers_map))  # <class 'map'>
    try:
        print(len(headers_map))
    except TypeError as e:
        print(str(e))  # object of type 'map' has no len()
    headers_list = list(headers_map)
    print(len(headers_list))  # 3

Similarly, if you do something like

values = map((lambda x: x.strip()), row)
my_cursor.execute(insert, values)

and get an error like

pyodbc.ProgrammingError: ('The SQL contains 7 parameter markers, but 1 parameters were supplied', 'HY000')

that's because values is a single <class 'map'> object, where pyodbc is expecting a list, tuple, or Row. So, define values as

values = list(map((lambda x: x.strip()), row))
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thank you! Progress! Error message has changed: `my_cursor.execute(insert, values) pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'TYPE'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'DATE'. (102); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'TIME'. (102); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")` – 츄 plus Dec 05 '19 at 01:44
  • Now works - thank you! The error above was generated because my header row in the CSV didn't exactly match the header row in the SQL table. – 츄 plus Dec 05 '19 at 02:18