Since oursql implements the DB-API, the basics of how to use it should be straightforward and explained in detail in other documents. This tutorial will thus mostly focus on behavior that is specific to oursql.
To make a connection, there’s the oursql.connect() function:
conn = oursql.connect(host='127.0.0.1', user='habnabit', passwd='foobar', db='example', port=3307)
By default, oursql will try to use unicode wherever possible, and set the connection charset to utf8. To use the database’s default connection charset, pass a charset of None:
conn = oursql.connect(db='example', charset=None)
It’s sometimes necessary to disable unicode handling, such as if there is malformed data in the database and not every row can be decoded using the same encoding. oursql has the option of completely ignoring unicode:
conn = oursql.connect(db='example', use_unicode=False, charset=None)
Cursors are created by calling the cursor() method on a connection. This method also allows specification of the cursor class and other extra options for the cursor:
curs = conn.cursor(oursql.DictCursor) curs = conn.cursor(try_plain_query=False)
The available cursor options are also documented on the cursor() method.
oursql uses the qmark parameterization style; placeholders in queries are represented using a single question mark:
curs.execute( 'SELECT * FROM `some_table` WHERE `col1` = ? AND `col2` = ?', (42, -3))
Queries and data are sent to the database server completely separately, alleviating the need for escaping data.
oursql also provides a number of ways to insert large amounts of data without buffering it all in memory. For inserting long string values, there’s IterWrapper and FileWrapper, and for long binary values, there’s BinaryIterWrapper and BinaryFileWrapper. Usage is very straightforward:
iw = oursql.IterWrapper([u'foo', u'bar']) fw = oursql.FileWrapper(codecs.open('some_file.txt', encoding='utf8')) biw = oursql.BinaryIterWrapper(['spam', 'eggs']) bfw = oursql.BinaryFileWrapper(open('some_blob.txt', 'rb')) curs.execute( 'INSERT INTO `some_table` VALUES (?, ?, ?, ?)', (iw, fw, biw, bfw))
Instances of these types will cause oursql to insert the data pieces at a time instead of all at once, buffering server-side instead of client-side.
Additionally, executemany() is lazy; if passed a generator or any other iterator which does produces values lazily, values will only be taken from the iterator immediately before they are sent to the database.
By default, oursql will not buffer the results of a query client-side. The only times oursql will ever buffer results client-side are:
Using the string_limit cursor option, oursql also provides a way to fetch long string or binary data from the database as needed instead of requiring an entire row to be buffered in memory:
curs = conn.cursor(string_limit=8192)
If string_limit is not None, oursql will return a file-like object in place of a string for any string or binary column. If the length of the data in the column is less than or equal to the string_limit, the data will be buffered client-side. Otherwise, all data will only be fetched from the database as requested. However, there are some caveats:
To ensure a clear separation between text and binary data, oursql requires that binary data being inserted into the database be wrapped in a buffer. Turning a bytestring into a buffer is a cheap operation and does not involve any copying:
some_binary_data = '\xde\xad\xbe\xef' curs.execute('INSERT INTO `some_table` VALUES (?)', (buffer(some_binary_data),))
Binary data coming back from the database will be of type str.
oursql implements the standard hierarchy of DB-API exceptions (see PEP 249), as well as PermissionsError (which should be self-explanatory) and CollatedWarningsError. Unless raise_on_warnings is explicitly turned off when calling oursql.connect(), any warnings or errors generated by running a query will be collected together and raised as a CollatedWarningsError. A list of (type, (message, errno)) tuples will be accessible as the extra attribute, where type is an oursql exception class.
A dict mapping MySQL error names to errnos is also exposed as oursql.errnos for more easily trapping specific errors.