Imploding a list for use in a Python MySQL IN clause

Use the list_of_ids directly: format_strings=”,”.join([‘%s’] * len(list_of_ids)) cursor.execute(“DELETE FROM WHERE baz IN (%s)” % format_strings, tuple(list_of_ids)) That way you avoid having to quote yourself, and avoid all kinds of sql injection. Note that the data (list_of_ids) is going directly to mysql’s driver, as a parameter (not in the query text) so there is no … Read more

MySQL: Get character-set of database or table or column?

Here’s how I’d do it – For Schemas (or Databases – they are synonyms): SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = “mydatabasename”; For Tables: SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = “mydatabasename” AND T.table_name = “tablename”; For Columns: SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_schema = “mydatabasename” AND table_name … Read more

Node.js returning result from MySQL query

You have to do the processing on the results from the db query on a callback only. Just like. function getColour(username, roomCount, callback) { connection.query(‘SELECT hexcode FROM colours WHERE precedence = ?’, [roomCount], function(err, result) { if (err) callback(err,null); else callback(null,result[0].hexcode); }); } //call Fn for db query with callback getColour(“yourname”,4, function(err,data){ if (err) { … Read more

In SQL how do I get the maximum value for an integer?

In Mysql there is a cheap trick to do this: mysql> select ~0; +———————-+ | ~0 | +———————-+ | 18446744073709551615 | +———————-+ the tilde is the bitwise negation. The resulting value is a bigint. See: For the other integer flavours, you can use the right bitshift operator >> like so: SELECT ~0 as max_bigint_unsigned … Read more

MySQL Query to calculate the Previous Month

Here you go, use this to get the date between the 1st of last month and the last of last month in MySQL: … order_placed_date BETWEEN DATE_FORMAT(NOW() – INTERVAL 1 MONTH, ‘%Y-%m-01 00:00:00’) AND DATE_FORMAT(LAST_DAY(NOW() – INTERVAL 1 MONTH), ‘%Y-%m-%d 23:59:59’)

Using MySQL triggers to log all table changes to a secondary table

The insert syntax is INSERT INTO table (columns_list) VALUES (values_list) so your insert would look something like this (i’m not a MySQL expert but you can fit the query): INSERT INTO data_tracking (`data_id` , `field` , `old_value` , `new_value` , `modified` ) VALUES (NEW.data_id, ‘field1’, OLD.field, NEW.field, CURRENT_DATETIME()); And repeat it for variation on field2 … Read more