python mssql bulk_python sqlalchemy在元组数据结构中插入多行

最新推荐文章于 2022-05-09 18:25:28 发布

weixin_39979617

最新推荐文章于 2022-05-09 18:25:28 发布

阅读量111

点赞数

文章标签： python mssql bulk

我一直在研究如何在数据库中插入具有7个元素(列)的约500个元组(行)的列表.我已经阅读了关于stackoverflow的各种帖子以及其他论坛.我发现了以下内容,并建议使用“ executemany()”方法,但对我来说不清楚.我是否需要将对象从元组转换为字典？问题是我没有数据结构的name：value类型.

Here is an example:

engine = create_engine('sqlite:///:memory:', echo=True)

metadata = MetaData()

hockey= Table('hockey', metadata,

Column('team', String(16), primary_key=True),

Column('jersey_colour', String(16)),

Column('stadium', String(32)),

Column('goals', Integer),

Column('date', Date, primary_key=True),

Column('assists', Integer))

>>>data[0]

[(u'Maple Leafs', u'Blue', u'Air Canada Center', 151, '2013-03-25', 301)]

编辑：

markers = ','.join('?' * len(data[0]))

ins = 'INSERT INTO {tablename} VALUES ({markers})'

ins = ins.format(tablename=hockey.name, markers=markers)

>>str(ins)

'INSERT INTO hockey VALUES (?,?,?,?,?,?)'

conn = engine.connect()

result = conn.execute(ins, data)

In [59]: result = conn.execute(ins, data)

2013-03-26 07:29:28,371 INFO sqlalchemy.engine.base.Engine INSERT INTO hockey VALUES (?,?,?,?,?,?)

2013-03-26 07:29:28,371 INFO sqlalchemy.engine.base.Engine (u'Maple Leafs', u'Blue', u'Air Canada Center', 151, '2013-03-25', 301)

2013-03-26 07:29:28,371 INFO sqlalchemy.engine.base.Engine ROLLBACK

---------------------------------------------------------------------------

OperationalError Traceback (most recent call last)

in ()

----> 1 result = conn.execute(ins, data)

/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)

662 object,

663 multiparams,

--> 664 params)

665 else:

666 raise exc.InvalidRequestError(

/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_text(self, statement, multiparams, params)

806 statement,

807 parameters,

--> 808 statement, parameters

809 )

810 if self._has_events:

/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)

876 parameters,

877 cursor,

--> 878 context)

879 raise

880

/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)

869 statement,

870 parameters,

--> 871 context)

872 except Exception, e:

873 self._handle_dbapi_exception(

/usr/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)

318

319 def do_execute(self, cursor, statement, parameters, context=None):

--> 320 cursor.execute(statement, parameters)

321

322 def do_execute_no_params(self, cursor, statement, context=None):

OperationalError: (OperationalError) near "hockey": syntax error 'INSERT INTO hockey VALUES (?,?,?,?,?,?)' (u'Maple Leafs', u'Blue', u'Air Canada Center', 151, '2013-03-25', 301)

出现错误：

OperationalError: (OperationalError) near "hockey": syntax error 'INSERT INTO hockey VALUES (?,?,?,?,?,?)' (u'Maple Leafs', u'Blue', u'Air Canada Center', 151, '2013-03-25', 301)