上野真 <[email protected]> wrote:
> Hi!
>
> I am using SQLAlchemy 0.9.8 and py-postgresql 1.1.0.
I’d suggest using the psycopg2 driver as it is much better supported.
>
> When I used the Column name without parentheses, it works perfectly.
It is a really, really bad idea to use characters like parenthesis and brackets
in the names of columns and tables and I’d strongly recommend not doing this.
The Insert statement allows the parameters for the columns to be passed using
the column names themselves, which means the bound parameter names have
parenthesis in them here (you can see this using echo=True on your engine).
This doesn’t do very well when then names have characters that themselves are
wrapped up in the bound parameter format itself (e.g. %(name)s), a workaround
is to assign keys to these columns and use those:
users = Table('test_table', metadata,
Column('E(1)', String, key='e1', ),
Column('E(2)', Float, key='e2', ),
Column('E[3]', Integer, key='e3')
)
metadata.create_all(engine)
ins = users.insert().values(
{'e1': "FOO", 'e2': 1000.0, 'e3': 5})
Another approach is to use a non-named paramstyle for the engine, it has to be
supported by the driver; this works for psycopg2:
engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True,
paramstyle="format")
then you get a statement like INSERT INTO test_table ("E(1)", "E(2)", "E[3]")
VALUES (%s, %s, %s), instead of INSERT INTO test_table ("E(1)", "E(2)", "E[3]")
VALUES (%(E(1))s, %(E(2))s, %(E[3])s), which is what’s not compatible here.
> But, the following code receives KeyError.
>
> from sqlalchemy import create_engine, Table, Column, Integer, Float, String,
> MetaData
> engine =
> create_engine("postgresql+pypostgresql://tester:xxxxx@localhost/test_db")
> engine.execute("DROP TABLE IF EXISTS test_table")
> metadata = MetaData()
> users = Table('test_table', metadata,
> Column('E(1)', String),
> Column('E(2)', Float),
> Column('E[3]', Integer)
> )
> metadata.create_all(engine)
> ins = users.insert().values(
> {'E(1)': "FOO", 'E(2)': 1000.0, 'E[3]': 5})
> conn = engine.connect()
> result = conn.execute(ins)
> result.close()
>
> The error is as follows
> Traceback (most recent call last):
> File "/Users/hoge/PycharmProjects/sqlalchemy_tutorial/PostgreSQL_test.py",
> line 30, in <module>
> result = conn.execute(ins)
> File
> "/Users/hoge/pyvenv/anaconda3/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
> line 729, in execute
> return meth(self, multiparams, params)
> File
> "/Users/hoge/pyvenv/anaconda3/lib/python3.4/site-packages/sqlalchemy/sql/elements.py",
> line 322, in _execute_on_connection
> return connection._execute_clauseelement(self, multiparams, params)
> File
> "/Users/hoge/pyvenv/anaconda3/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
> line 826, in _execute_clauseelement
> compiled_sql, distilled_params
> File
> "/Users/hoge/pyvenv/anaconda3/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
> line 958, in _execute_context
> context)
> File
> "/Users/hoge/pyvenv/anaconda3/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
> line 1162, in _handle_dbapi_exception
> util.reraise(*exc_info)
> File
> "/Users/hoge/pyvenv/anaconda3/lib/python3.4/site-packages/sqlalchemy/util/compat.py",
> line 182, in reraise
> raise value
> File
> "/Users/hoge/pyvenv/anaconda3/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
> line 951, in _execute_context
> context)
> File
> "/Users/hoge/pyvenv/anaconda3/lib/python3.4/site-packages/sqlalchemy/engine/default.py",
> line 436, in do_execute
> cursor.execute(statement, parameters)
> File
> "/Users/hoge/.pyenv/versions/anaconda3-2.1.0/lib/python3.4/site-packages/postgresql/driver/dbapi20.py",
> line 264, in execute
> sql, pxf, nparams = self._convert_query(statement)
> File
> "/Users/hoge/.pyenv/versions/anaconda3-2.1.0/lib/python3.4/site-packages/postgresql/driver/dbapi20.py",
> line 244, in _convert_query
> rparts.append(part % kmap)
> KeyError: 'E(1)'
>
> Thank you.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/sqlalchemy
> <http://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
