how to bulk_query?

[mouday]

i saw:

https://docs.sqlalchemy.org/en/latest/core/connections.html?highlight=multiparams#sqlalchemy.engine.Connection.execute.params.object

but it not work, please tell me how to use bulk_query?

[Thrimbda]

bulk query only works when you do update or insert operation, and it works fine for me as the documentation elaborated.

can you give a more specific use case detailing how it didn't work? Does it raise an exception or just return nothing?

[mouday]

i try this, but Exception

import records

db = records.Database('mysql://root:ooxx@localhost/demo')

db.bulk_query("insert names(name, age) values(?, ?)", ("Tom", 23), ("Jimi", 22))

[Thrimbda]

take a look at the source code, at bulk_query API, it takes a list of kwarg.

as sqlalchemy says:

conn.execute(
    table.insert(),
    {"id":1, "value":"v1"},
    {"id":2, "value":"v2"}
)

in your case, it should be something like this:

import records

db = records.Database('mysql://root:ooxx@localhost/demo')

db.bulk_query("insert names(name, age) values(?, ?)", [{"Tom": 23}, {"Jimi", 22}])

have a try

[mouday]

i try, but have a Exception

sqlalchemy.exc.ProgrammingError

[Thrimbda]

please give the content of that exception, and a ProgramingError is usually a issue of your sql

[mouday]

my Exception 👍

Traceback (most recent call last):
  File "/Users/qmp/.virtualenvs/py3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1170, in _execute_context
    context)
  File "/Users/qmp/.virtualenvs/py3/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 105, in do_executemany
    rowcount = cursor.executemany(statement, parameters)
  File "/Users/qmp/.virtualenvs/py3/lib/python3.6/site-packages/MySQLdb/cursors.py", line 283, in executemany
    self.rowcount = sum(self.execute(query, arg) for arg in args)
  File "/Users/qmp/.virtualenvs/py3/lib/python3.6/site-packages/MySQLdb/cursors.py", line 283, in <genexpr>
    self.rowcount = sum(self.execute(query, arg) for arg in args)
  File "/Users/qmp/.virtualenvs/py3/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/Users/qmp/.virtualenvs/py3/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/Users/qmp/.virtualenvs/py3/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/Users/qmp/.virtualenvs/py3/lib/python3.6/site-packages/MySQLdb/cursors.py", line 412, in _query
    rowcount = self._do_query(q)
  File "/Users/qmp/.virtualenvs/py3/lib/python3.6/site-packages/MySQLdb/cursors.py", line 375, in _do_query
    db.query(q)
  File "/Users/qmp/.virtualenvs/py3/lib/python3.6/site-packages/MySQLdb/connections.py", line 276, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?)' at line 1")

[mouday]

and this is my table:

mysql> select * from names;

+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | 大红   |   24 |
|  3 | 大壮   |   24 |
|  4 | 秀英   |   24 |
|  6 | 小明   |   23 |
|  7 | 大名   |   23 |
| 10 | 壮壮   |   25 |
+----+--------+------+
"""

[mouday]

do you have any example?

[Thrimbda]

you should explicit gave variable names, I didn't take a close look at your dml- -, sorry for that.

this should be working

db.bulk_query("insert names(name, age) values(:name, :age)",
              [{'name': 'Tom', 'age': 23}, {'name': 'Jimi', 'age': 22}])

[mouday]

ok, it is work, thank you very very very!

can you tell me , where are you see this?

[Thrimbda]

the source code itself is already a good documentation. have a loot at bulk_query API, it accepts a destructuring param list,

def bulk_query(self, query, *multiparams):
    """Bulk insert or update."""

    self._conn.execute(text(query), *multiparams)

with some experimentation, you'll know how to use it.

[mouday]

but。。。 i know pymysql is this:

data = [
    ("student1", 23, "2018-01-31", "M"),
    ("student2", 24, "2018-02-27", "M"),
    ("student3", 28, "2018-03-31", "F"),
    ("student4", 26, "2018-04-30", "M"),
]

rows = cursor.executemany("insert into student(name, age, register_date, gender) "
                               "values (%s, %s, %s, %s)", data)

but it not work,

however, document not write how to use...

finally, thank you agan!