MySQLdb is a thin python wrapper around C module which implements API for MySQL database. There was MySQLDb1 version of wrapper used some time ago and now it is considered to be a legacy. As MySQLDb1 started evolving to MySQLDb2 with bug fixes and Python3 support, a MySQLDb1 was forked and here is how mysqlclient … Read more
Databases have different notations for “quoting” identifiers (table and column names etc) and values (data). MySQL uses backticks to quote identifiers. For values, it’s best to use the parameter substitution mechanism provided by the connector package: it’s more likely to handle tricky cases like embedded quotes correctly, and will reduce the risk of SQL injection. … Read more
This : Error not in (‘Timeout’,’Connection Error’); is semantically equivalent to: Error <> ‘TimeOut’ AND Error <> ‘Connection Error’ Rules about null comparison applies to IN too. So if the value of Error is NULL, the database can’t make the expression true. To fix, you could do this: COALESCE(Error,”) not in (‘Timeout’,’Connection Error’); Or better … Read more
I will re-iterate the best practice at everyone who comes across the sql connection using MySQLdb or any other package to connect python2/3 needs to know this (Following mock run assumes that you have a table named tablename in your sql database. It has got 4 columns/fields with names field1,field2,field3,field4). If your connection is local … Read more
I solved it myself. I use the wheel installer from http://www.lfd.uci.edu/~gohlke/pythonlibs/#mysql-python. There are two wheel packages there. The amd64 one refuses to install on my platform (Windows) but the other one works just fine. I mean the file with this name: MySQL_python-1.2.5-cp27-none-win32.whl Then install it by running this below command in the same folder with … Read more
If you are using the default cursor, a MySQLdb.cursors.Cursor, the entire result set will be stored on the client side (i.e. in a Python list) by the time the cursor.execute() is completed. Therefore, even if you use for row in cursor: you will not be getting any reduction in memory footprint. The entire result set … Read more
We found an attribute on the cursor object called cursor._last_executed that holds the last query string to run even when an exception occurs. This was easier and better for us in production than using profiling all the time or MySQL query logging as both of those have a performance impact and involve more code or more … Read more
If you’re having issues compiling the binary extension, or on a platform where you cant, you can try using the pure python PyMySQL bindings. Simply pip install pymysql and switch your SQLAlchemy URI to start like this: SQLALCHEMY_DATABASE_URI = ‘mysql+pymysql://…..’ There are some other drivers you could also try.
cursor.description will give you a tuple of tuples where [0] for each is the column header. num_fields = len(cursor.description) field_names = [i[0] for i in cursor.description]
Parameter substitution in the DB API is only for values – not tables or fields. You’ll need to use normal string substitution for those: selectQ =”””SELECT * FROM %s WHERE %s = %%s;””” % (self.table,self.columnSpecName) self.db.execute(selectQ,(idKey,)) return self.db.store_result() Note that the value placeholder has a double % – this is so that it’s left alone … Read more