0

I'm a novice using MySQL, and I've got stuck with a very complex (for me) query...

it's this one:

numbers = [1,2,3]
for number in numbers:
            self.db.cursor().execute("""(SELECT 'bla', 'bla', 'bla', 'bla', 'bla') 
                                         UNION 
                                         (SELECT bla, bla, bla, bla, bla 
                                         FROM table 
                                         WHERE num = %s AND date = 'YY-MM-DD'
                                         INTO OUTFILE CONCAT('/var/lib/mysql-files/file_', %s, '.csv') 
                                         FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' 
                                         LINES TERMINATED BY '\r\n')""", (number, number))

When I run the query I get this:

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 \'CONCAT(\'/var/lib/mysql-files/file_\', 1, \'h.csv\') FIELDS ENCLOSED BY \'"\' TERMINAT\' at line 6')

I really think it's because of the way I'm trying to insert the variables into the query, cos if I replace the ´%s´ with a static number, it goes perfectly! (WHERE num = 1, /var/lib/mysql-files/file_1.csv') I've tried many things, but I've gotten nothing...only to make myself a huge mess :(

So, how could I properly insert the variable 'number' in those places?

These are the links I've visited and tried:

Using a Python variable in MySQL query

Python MySQL executemany in WHERE clause

Community
  • 1
  • 1
wj127
  • 118
  • 1
  • 12
  • Change `%s` to `?`? – roganjosh Feb 07 '17 at 16:29
  • You need a % instead of a comma between the query string and the tuple of variables """SELECT... %s...%s...""" % (var1, var2) – James Doepp - pihentagyu Feb 07 '17 at 16:37
  • Your problem is not passing in the parameters; it is that you can't use CONCAT there at all. – Daniel Roseman Feb 07 '17 at 16:38
  • @roganjosh : I've tried what you said, but didn't work. I got this error `query = query % tuple([db.literal(item) for item in args]) TypeError: not all arguments converted during string formatting`. I tried to cast them with str(), but got the same problem... – wj127 Feb 07 '17 at 16:44
  • @DanielRoseman : I've done your proposal, but nothing, got the same error: `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 \'CONCAT(\'/var/lib/mysql-files/file_\', 1, \'h.csv\') FIELDS ENCLOSED BY \'"\' TERMINAT\' at line 6')` – wj127 Feb 07 '17 at 16:45
  • @DanielRoseman : Then, how could I achieve what I want without the `CONCAT()` ? – wj127 Feb 07 '17 at 16:46

0 Answers0