Python: Cannot concatenate str and NoneType objects

Question

sql = """
        INSERT INTO [SCHOOLINFO] 
        VALUES(
            '""" + self.accountNo + """', 
            '""" + self.altName + """',
            '""" + self.address1 + """',
            '""" + self.address2 + """',
            '""" + self.city + """',
            '""" + self.state + """',
            '""" + self.zipCode + """',
            '""" + self.phone1 + """',
            '""" + self.phone2 + """',
            '""" + self.fax + """',
            '""" + self.contactName + """',
            '""" + self.contactEmail + """',
            '""" + self.prize_id + """',
            '""" + self.shipping + """',
            '""" + self.chairTempPass + """',
            '""" + self.studentCount + """'
        )
    """;

I have the following code and Python keeps throwing the error that it cannon concatenate strings and nonetype objects. The thing is I have verified every variable here is in fact a string and is not null. I have been stuck on this for quite some time today, and any help would be greatly appreciated.

Answer 1

Use bind variables instead. Here's the spec for working with DBs in Python: PEP 249: Python Database API Specification v2.0.

UPDATE: Based on the docs for pymssql, you need something like:

sql = """
    INSERT INTO [SCHOOLINFO] 
    VALUES(
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %s, %s, %d
    )"""
cur.execute(sql, self.accountNo, self.altName, self.address1, self.address2, self.city, self.state, self.zipCode, self.phone1, self.phone2, self.fax, self.contactName, self.contactEmail, self.prize_id, self.shipping, self.chairTempPass, self.studentCount)

Answer 2

You should not be concatenating values into SQL statements, because doing so leaves you wide open to (accidental or deliberate) SQL injection. Instead, you should be passing SQL statements with parameter markers where the values should go, and letting your database connector insert the values into the correct places.

All database connectors support this in some form. For example, psycopg2 (a PostgreSQL connector which follows the Python DB API) would accept something like this:

cursor.execute( "insert into schoolinfo (accountno, altname) values (%s, %s)",
    (self.accountNo, self.altName))

A side benefit of doing it this way: Values that are not strings (e.g. None) will be converted automatically and you won't get the error you described in your question.

Answer 3

Composing the SQL query like this is very dangerous, especially due to sql-injection

If using MySqldb a better alternative would be like this:

db.query("INSERT INTO [SCHOOLINFO] VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
[self.accountNo, self.altName, self.address1, self.address2, self.city, self.state, self.zipCode, self.phone1, self.phone2, self.fax, self.contactName, self.contactEmail, self.prize_id, self.shipping, self.chairTempPass, self.studentCount])

Answer 4

I'm going to assume you're using a library like MySQLdb. The best way to handle these kind of statements is like so:

import _mysql

db = _mysql.connect("localhost","user","password","database_name")
cursor = db.cursor()

sql = """
    INSERT INTO [SCHOOLINFO] 
    VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
cursor.execute(sql, [self.accountNo, self.altName, self.address1, \
                     self.address2, self.city, self.state, self.zipCode, \
                     self.phone1, self.phone2, self.fax, self.contactName, \
                     self.contactEmail, self.prize_id, self.shipping, \
                     self.chairTempPass, self.studentCount])

This way the database library handles properly entering the values in the INSERT query. It'll even make None values be entered as NULL into the new row. Plus the original way you were doing it was pretty susceptible to SQL injection attacks.

If you aren't using mysql, your library probably has similar functionality.

EDIT -

If you're connecting to a SQL Server database, use the pyodbc library. You can get it at http://code.google.com/p/pyodbc/. Here's what the code would look like:

import pyodbc

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=database_name;UID=user;PWD=password')
cursor = conn.cursor()

sql = """
    INSERT INTO [SCHOOLINFO] 
    VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
cursor.execute(sql, self.accountNo, self.altName, self.address1, \
                     self.address2, self.city, self.state, self.zipCode, \
                     self.phone1, self.phone2, self.fax, self.contactName, \
                     self.contactEmail, self.prize_id, self.shipping, \
                     self.chairTempPass, self.studentCount)
conn.commit()

Answer 5

All these answers so far focus not on your problem but on what is right to do. Yes, yes - bind variables is better and safer. And yes, using % for formatting is faster and likely better.

But on your question what gives you that error - it must be that one of the values is None at some point, there is no other explanation. Just put a debug print in front of that, something like:

for v in 'accountNo altName address1 address2 city state zipCode phone1 phone2 fax contactName contactEmail prize_id shipping chairTempPass studentCount'.split():
    if getattr(self, v) is None:
        print 'PANIC: %s is None' % v

I bet it will print something at some point ;-)