Sqlalchemy Insert - AttributeError: object has no attribute 'items'

Question

I have a msyql table with a datetime column. I am trying to use python's datetime.datetime.now() and insert into it with sqlalchemy core.

from sqlalchemy import MetaData, create_engine, Table, insert 
import datetime 
# from sqlalchemy.sql import func
engine = create_engine('...') 
conn = engine.connect() 

tablemeta = MetaData(bind=engine, reflect=True) 

datetime_test_table = Table('datetime_test', tablemeta, autoload=True, 
        autoload_with=engine) 

ins = insert(datetime_test_table).values(datetime.datetime.now()) 
conn.execute(ins)

I get the following error :

AttributeError: 'datetime.datetime' object has no attribute 'items'

I tried using func.now() as well, but a similar error occurs. What object should I use to do the insertion?

Answer 1

From the docs on Insert.values()

To pass values to an INSERT statement, you can use keyword args:

users.insert().values(name="some name")

Or you can pass a single positional arg:

a dictionary, tuple, or list of dictionaries or tuples can be passed as a single positional argument

users.insert().values({"name": "some name"})  # passing a single dict
users.insert().values((5, "some name"))  # passing a tuple (could be list too).

For tuples/lists of values, SQLAlchemy will turn that into a dictionary by iterating over the table columns and the collection of values. This is the function that processes a single positional arg to .values() (permalink):

def process_single(p):
    if isinstance(p, (list, tuple)):
        return dict(
            (c.key, pval)
            for c, pval in zip(self.table.c, p)
        )
    else:
        return p  # this is where it is assumed that your datetime object is a dict

So, it's documented that your single positional arg to .values() can be any of a dict, list or tuple and in the above function, you can see that if you pass a list or tuple of values, they are converted into a dict. If your single positional arg is not a list or tuple, it is simply returned, under the assumption that it is a dict (I've annotated where that assumption is made in the example above).

This is what brings about your error. You are passing a single positional arg to .values() that is neither a list or tuple, so SQLAlchemy assumes it is a dict. But, it's not a dict, it's a datetime.datetime instance. The error...

AttributeError: 'datetime.datetime' object has no attribute 'items'

...is simply the first time that SQLAlchemy performs a dict specific operation on your parameter object after making the assumption that it is a dict.

To fix your issue, simply pass the values in an acceptable format, any of these will do:

ins = insert(datetime_test_table).values((datetime.datetime.now(),))
ins = insert(datetime_test_table).values([datetime.datetime.now()])
ins = insert(datetime_test_table).values({'dt_col_name': datetime.datetime.now()})
ins = insert(datetime_test_table).values(dt_col_name=datetime.datetime.now())