title: SQLAlchemy Expression Language - INSERT SELECT with literal values
body: <p>I've just started using the SQLAlchemy SQL Expression Language, and I'm trying to insert into a table values from an existing tables and literal values. I'm trying to emulate this SQL statement in the SQL Expression language:</p>
  
  <pre><code>INSERT INTO b_Customers 
  (CustomerID, 
  CustomerName, 
  CustomerType,
  IsCurrent,
  FirstObserved,
  LastObserved)
  
  SELECT CustomerID, CustomerName, CustomerType, 1, '2018-01-23', '2018-01-23'
  FROM s_Customers
  </code></pre>
  
  <p>Here's some setup Python code I'm running:</p>
  
  <pre><code>from datetime import datetime
  from io import StringIO
  import csv
  
  from sqlalchemy import create_engine, Table, Column, Integer, Date, String, MetaData, ForeignKey, bindparam, text
  from sqlalchemy.sql import select
  from sqlalchemy.sql.expression import literal_column, literal
  
  engine = create_engine('sqlite:///:memory:')
  
  metadata = MetaData()
  stage = Table('s_Customers', metadata,
      Column('CustomerID', Integer, primary_key=True),
      Column('CustomerName', String),
      Column('CustomerType', String),
  )
  
  base = Table('b_Customers', metadata,
      Column('CustomerID', Integer, primary_key=True),
      Column('CustomerName', String),
      Column('CustomerType', String),
      Column('IsCurrent', Integer),
      Column('FirstObserved', Date),
      Column('LastObserved', Date),
  )
  
  metadata.create_all(engine)
  
  insert_data = u'''CustomerID,CustomerName,CustomerType
  1,Jannet,Preferred
  2,Daniel,Regular
  3,Casper,Regular'''
  
  table_to_insert = StringIO(insert_data)
  table_values = list(csv.DictReader(table_to_insert))
  
  conn = engine.connect()
  stmt = stage.insert().values(table_values)
  conn.execute(stmt)
  </code></pre>
  
  <p>And here's where I'm stuck:</p>
  
  <pre><code>stmt = base.insert().from_select(list(stage.columns) + [bindparam(1, 'IsCurrent'), 
                                                          bindparam(datetime(2018, 1, 23), 'FirstObserved'), 
                                                          bindparam(datetime(2018, 1, 23), 'LastObserved')],
                                  stage)
  conn.execute(stmt)
  </code></pre>
  
  <p>Here's the stack trace from running the above code:</p>
  
  <pre><code>---------------------------------------------------------------------------
  KeyError                                  Traceback (most recent call last)
  &lt;ipython-input-35-a3d52b22c83e&gt; in &lt;module&gt;()
        3                                                         bindparam(datetime(2018, 1, 23), 'LastObserved')],
        4                                 stage)
  ----&gt; 5 conn.execute(stmt)
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\engine\base.pyc in execute(self, object, *multiparams, **params)
      946             raise exc.ObjectNotExecutableError(object)
      947         else:
  --&gt; 948             return meth(self, multiparams, params)
      949 
      950     def _execute_function(self, func, multiparams, params):
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\sql\elements.pyc in _execute_on_connection(self, connection, multiparams, params)
      267     def _execute_on_connection(self, connection, multiparams, params):
      268         if self.supports_execution:
  --&gt; 269             return connection._execute_clauseelement(self, multiparams, params)
      270         else:
      271             raise exc.ObjectNotExecutableError(self)
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\engine\base.pyc in _execute_clauseelement(self, elem, multiparams, params)
     1051                 inline=len(distilled_params) &gt; 1,
     1052                 schema_translate_map=self.schema_for_object
  -&gt; 1053                 if not self.schema_for_object.is_default else None)
     1054 
     1055         ret = self._execute_context(
  
  &lt;string&gt; in &lt;lambda&gt;(self, bind, dialect, **kw)
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\sql\elements.pyc in compile(self, default, bind, dialect, **kw)
      440             else:
      441                 dialect = default.StrCompileDialect()
  --&gt; 442         return self._compiler(dialect, bind=bind, **kw)
      443 
      444     def _compiler(self, dialect, **kw):
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\sql\elements.pyc in _compiler(self, dialect, **kw)
      446         Dialect."""
      447 
  --&gt; 448         return dialect.statement_compiler(dialect, self, **kw)
      449 
      450     def __str__(self):
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\sql\compiler.pyc in __init__(self, dialect, statement, column_keys, inline, **kwargs)
      451         # dialect.label_length or dialect.max_identifier_length
      452         self.truncated_names = {}
  --&gt; 453         Compiled.__init__(self, dialect, statement, **kwargs)
      454 
      455         if (
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\sql\compiler.pyc in __init__(self, dialect, statement, bind, schema_translate_map, compile_kwargs)
      217             if self.can_execute:
      218                 self.execution_options = statement._execution_options
  --&gt; 219             self.string = self.process(self.statement, **compile_kwargs)
      220 
      221     @util.deprecated("0.7", ":class:`.Compiled` objects now compile "
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\sql\compiler.pyc in process(self, obj, **kwargs)
      243 
      244     def process(self, obj, **kwargs):
  --&gt; 245         return obj._compiler_dispatch(self, **kwargs)
      246 
      247     def __str__(self):
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\sql\visitors.pyc in _compiler_dispatch(self, visitor, **kw)
       79                     raise exc.UnsupportedCompilationError(visitor, cls)
       80                 else:
  ---&gt; 81                     return meth(self, **kw)
       82         else:
       83             # The optimization opportunity is lost for this case because the
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\sql\compiler.pyc in visit_insert(self, insert_stmt, asfrom, **kw)
     2032 
     2033         crud_params = crud._setup_crud_params(
  -&gt; 2034             self, insert_stmt, crud.ISINSERT, **kw)
     2035 
     2036         if not crud_params and \
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\sql\crud.pyc in _setup_crud_params(compiler, stmt, local_stmt_type, **kw)
       55     try:
       56         if local_stmt_type in (ISINSERT, ISUPDATE):
  ---&gt; 57             return _get_crud_params(compiler, stmt, **kw)
       58     finally:
       59         if should_restore:
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\sql\crud.pyc in _get_crud_params(compiler, stmt, **kw)
      130             compiler, stmt, parameters,
      131             _getattr_col_key, _column_as_key,
  --&gt; 132             _col_bind_name, check_columns, values, kw)
      133     else:
      134         _scan_cols(
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\sql\crud.pyc in _scan_insert_from_select_cols(compiler, stmt, parameters, _getattr_col_key, _column_as_key, _col_bind_name, check_columns, values, kw)
      211 
      212     cols = [stmt.table.c[_column_as_key(name)]
  --&gt; 213             for name in stmt.select_names]
      214 
      215     compiler._insert_from_select = stmt.select
  
  C:\Users\rdelgado\AppData\Local\Continuum\Anaconda2\Lib\site-packages\sqlalchemy\util\_collections.pyc in __getitem__(self, key)
      192 
      193     def __getitem__(self, key):
  --&gt; 194         return self._data[key]
      195 
      196     def __delitem__(self, key):
  
  KeyError: 1
  </code></pre>
  
  <p>I've also tried using literal() and literal_column() and those haven't been successful.</p>
  
  <p>Thanks for any help!</p>