CODEWITHSUNDEEP
pythonsql-serverpython-3.xcsvpyodbc
츄 plus
츄 plus

Reputation: 514

TypeError: object of type 'map' has no len() when trying to insert a CSV into an SQL Server database in Python 3

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)

Upvotes: 1

Views: 303

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

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))

Upvotes: 1

Related Questions