MySQLdb.txt 28 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718
  1. ====================
  2. MySQLdb User's Guide
  3. ====================
  4. .. contents::
  5. ..
  6. Introduction
  7. ------------
  8. MySQLdb is an thread-compatible interface to the popular MySQL
  9. database server that provides the Python database API.
  10. Installation
  11. ------------
  12. The ``README`` file has complete installation instructions.
  13. _mysql
  14. ------
  15. If you want to write applications which are portable across databases,
  16. use MySQLdb_, and avoid using this module directly. ``_mysql``
  17. provides an interface which mostly implements the MySQL C API. For
  18. more information, see the `MySQL documentation`_. The documentation
  19. for this module is intentionally weak because you probably should use
  20. the higher-level MySQLdb module. If you really need it, use the
  21. standard MySQL docs and transliterate as necessary.
  22. .. _`MySQL documentation`: http://dev.mysql.com/doc/
  23. MySQL C API translation
  24. .......................
  25. The MySQL C API has been wrapped in an object-oriented way. The only
  26. MySQL data structures which are implemented are the ``MYSQL``
  27. (database connection handle) and ``MYSQL_RES`` (result handle)
  28. types. In general, any function which takes ``MYSQL *mysql`` as an
  29. argument is now a method of the connection object, and any function
  30. which takes ``MYSQL_RES *result`` as an argument is a method of the
  31. result object. Functions requiring none of the MySQL data structures
  32. are implemented as functions in the module. Functions requiring one of
  33. the other MySQL data structures are generally not implemented.
  34. Deprecated functions are not implemented. In all cases, the ``mysql_``
  35. prefix is dropped from the name. Most of the ``conn`` methods listed
  36. are also available as MySQLdb Connection object methods. Their use is
  37. non-portable.
  38. MySQL C API function mapping
  39. ............................
  40. =================================== ==================================
  41. C API ``_mysql``
  42. =================================== ==================================
  43. ``mysql_affected_rows()`` ``conn.affected_rows()``
  44. ``mysql_autocommit()`` ``conn.autocommit()``
  45. ``mysql_character_set_name()`` ``conn.character_set_name()``
  46. ``mysql_close()`` ``conn.close()``
  47. ``mysql_commit()`` ``conn.commit()``
  48. ``mysql_connect()`` ``_mysql.connect()``
  49. ``mysql_data_seek()`` ``result.data_seek()``
  50. ``mysql_debug()`` ``_mysql.debug()``
  51. ``mysql_dump_debug_info`` ``conn.dump_debug_info()``
  52. ``mysql_escape_string()`` ``_mysql.escape_string()``
  53. ``mysql_fetch_row()`` ``result.fetch_row()``
  54. ``mysql_get_character_set_info()`` ``conn.get_character_set_info()``
  55. ``mysql_get_client_info()`` ``_mysql.get_client_info()``
  56. ``mysql_get_host_info()`` ``conn.get_host_info()``
  57. ``mysql_get_proto_info()`` ``conn.get_proto_info()``
  58. ``mysql_get_server_info()`` ``conn.get_server_info()``
  59. ``mysql_info()`` ``conn.info()``
  60. ``mysql_insert_id()`` ``conn.insert_id()``
  61. ``mysql_num_fields()`` ``result.num_fields()``
  62. ``mysql_num_rows()`` ``result.num_rows()``
  63. ``mysql_options()`` various options to ``_mysql.connect()``
  64. ``mysql_ping()`` ``conn.ping()``
  65. ``mysql_query()`` ``conn.query()``
  66. ``mysql_real_connect()`` ``_mysql.connect()``
  67. ``mysql_real_query()`` ``conn.query()``
  68. ``mysql_real_escape_string()`` ``conn.escape_string()``
  69. ``mysql_rollback()`` ``conn.rollback()``
  70. ``mysql_row_seek()`` ``result.row_seek()``
  71. ``mysql_row_tell()`` ``result.row_tell()``
  72. ``mysql_select_db()`` ``conn.select_db()``
  73. ``mysql_set_character_set()`` ``conn.set_character_set()``
  74. ``mysql_ssl_set()`` ``ssl`` option to ``_mysql.connect()``
  75. ``mysql_stat()`` ``conn.stat()``
  76. ``mysql_store_result()`` ``conn.store_result()``
  77. ``mysql_thread_id()`` ``conn.thread_id()``
  78. ``mysql_thread_safe_client()`` ``conn.thread_safe_client()``
  79. ``mysql_use_result()`` ``conn.use_result()``
  80. ``mysql_warning_count()`` ``conn.warning_count()``
  81. ``CLIENT_*`` ``MySQLdb.constants.CLIENT.*``
  82. ``CR_*`` ``MySQLdb.constants.CR.*``
  83. ``ER_*`` ``MySQLdb.constants.ER.*``
  84. ``FIELD_TYPE_*`` ``MySQLdb.constants.FIELD_TYPE.*``
  85. ``FLAG_*`` ``MySQLdb.constants.FLAG.*``
  86. =================================== ==================================
  87. Some _mysql examples
  88. ....................
  89. Okay, so you want to use ``_mysql`` anyway. Here are some examples.
  90. The simplest possible database connection is::
  91. import _mysql
  92. db=_mysql.connect()
  93. This creates a connection to the MySQL server running on the local
  94. machine using the standard UNIX socket (or named pipe on Windows),
  95. your login name (from the USER environment variable), no password, and
  96. does not ``USE`` a database. Chances are you need to supply more
  97. information.::
  98. db=_mysql.connect("localhost","joebob","moonpie","thangs")
  99. This creates a connection to the MySQL server running on the local
  100. machine via a UNIX socket (or named pipe), the user name "joebob", the
  101. password "moonpie", and selects the initial database "thangs".
  102. We haven't even begun to touch upon all the parameters ``connect()``
  103. can take. For this reason, I prefer to use keyword parameters::
  104. db=_mysql.connect(host="localhost",user="joebob",
  105. passwd="moonpie",db="thangs")
  106. This does exactly what the last example did, but is arguably easier to
  107. read. But since the default host is "localhost", and if your login
  108. name really was "joebob", you could shorten it to this::
  109. db=_mysql.connect(passwd="moonpie",db="thangs")
  110. UNIX sockets and named pipes don't work over a network, so if you
  111. specify a host other than localhost, TCP will be used, and you can
  112. specify an odd port if you need to (the default port is 3306)::
  113. db=_mysql.connect(host="outhouse",port=3307,passwd="moonpie",db="thangs")
  114. If you really had to, you could connect to the local host with TCP by
  115. specifying the full host name, or 127.0.0.1.
  116. Generally speaking, putting passwords in your code is not such a good
  117. idea::
  118. db=_mysql.connect(host="outhouse",db="thangs",read_default_file="~/.my.cnf")
  119. This does what the previous example does, but gets the username and
  120. password and other parameters from ~/.my.cnf (UNIX-like systems). Read
  121. about `option files`_ for more details.
  122. .. _`option files`: http://dev.mysql.com/doc/mysql/en/Option_files.html
  123. So now you have an open connection as ``db`` and want to do a
  124. query. Well, there are no cursors in MySQL, and no parameter
  125. substitution, so you have to pass a complete query string to
  126. ``db.query()``::
  127. db.query("""SELECT spam, eggs, sausage FROM breakfast
  128. WHERE price < 5""")
  129. There's no return value from this, but exceptions can be raised. The
  130. exceptions are defined in a separate module, ``_mysql_exceptions``,
  131. but ``_mysql`` exports them. Read DB API specification PEP-249_ to
  132. find out what they are, or you can use the catch-all ``MySQLError``.
  133. .. _PEP-249: http://www.python.org/peps/pep-0249.html
  134. At this point your query has been executed and you need to get the
  135. results. You have two options::
  136. r=db.store_result()
  137. # ...or...
  138. r=db.use_result()
  139. Both methods return a result object. What's the difference?
  140. ``store_result()`` returns the entire result set to the client
  141. immediately. If your result set is really large, this could be a
  142. problem. One way around this is to add a ``LIMIT`` clause to your
  143. query, to limit the number of rows returned. The other is to use
  144. ``use_result()``, which keeps the result set in the server and sends
  145. it row-by-row when you fetch. This does, however, tie up server
  146. resources, and it ties up the connection: You cannot do any more
  147. queries until you have fetched **all** the rows. Generally I
  148. recommend using ``store_result()`` unless your result set is really
  149. huge and you can't use ``LIMIT`` for some reason.
  150. Now, for actually getting real results::
  151. >>> r.fetch_row()
  152. (('3','2','0'),)
  153. This might look a little odd. The first thing you should know is,
  154. ``fetch_row()`` takes some additional parameters. The first one is,
  155. how many rows (``maxrows``) should be returned. By default, it returns
  156. one row. It may return fewer rows than you asked for, but never
  157. more. If you set ``maxrows=0``, it returns all rows of the result
  158. set. If you ever get an empty tuple back, you ran out of rows.
  159. The second parameter (``how``) tells it how the row should be
  160. represented. By default, it is zero which means, return as a tuple.
  161. ``how=1`` means, return it as a dictionary, where the keys are the
  162. column names, or ``table.column`` if there are two columns with the
  163. same name (say, from a join). ``how=2`` means the same as ``how=1``
  164. except that the keys are *always* ``table.column``; this is for
  165. compatibility with the old ``Mysqldb`` module.
  166. OK, so why did we get a 1-tuple with a tuple inside? Because we
  167. implicitly asked for one row, since we didn't specify ``maxrows``.
  168. The other oddity is: Assuming these are numeric columns, why are they
  169. returned as strings? Because MySQL returns all data as strings and
  170. expects you to convert it yourself. This would be a real pain in the
  171. ass, but in fact, ``_mysql`` can do this for you. (And ``MySQLdb``
  172. does do this for you.) To have automatic type conversion done, you
  173. need to create a type converter dictionary, and pass this to
  174. ``connect()`` as the ``conv`` keyword parameter.
  175. The keys of ``conv`` should be MySQL column types, which in the
  176. C API are ``FIELD_TYPE_*``. You can get these values like this::
  177. from MySQLdb.constants import FIELD_TYPE
  178. By default, any column type that can't be found in ``conv`` is
  179. returned as a string, which works for a lot of stuff. For our
  180. purposes, we probably want this::
  181. my_conv = { FIELD_TYPE.LONG: int }
  182. This means, if it's a ``FIELD_TYPE_LONG``, call the builtin ``int()``
  183. function on it. Note that ``FIELD_TYPE_LONG`` is an ``INTEGER``
  184. column, which corresponds to a C ``long``, which is also the type used
  185. for a normal Python integer. But beware: If it's really an ``UNSIGNED
  186. INTEGER`` column, this could cause overflows. For this reason,
  187. ``MySQLdb`` actually uses ``long()`` to do the conversion. But we'll
  188. ignore this potential problem for now.
  189. Then if you use ``db=_mysql.connect(conv=my_conv...)``, the
  190. results will come back ``((3, 2, 0),)``, which is what you would
  191. expect.
  192. MySQLdb
  193. -------
  194. MySQLdb is a thin Python wrapper around ``_mysql`` which makes it
  195. compatible with the Python DB API interface (version 2). In reality,
  196. a fair amount of the code which implements the API is in ``_mysql``
  197. for the sake of efficiency.
  198. The DB API specification PEP-249_ should be your primary guide for
  199. using this module. Only deviations from the spec and other
  200. database-dependent things will be documented here.
  201. Functions and attributes
  202. ........................
  203. Only a few top-level functions and attributes are defined within
  204. MySQLdb.
  205. connect(parameters...)
  206. Constructor for creating a connection to the
  207. database. Returns a Connection Object. Parameters are the
  208. same as for the MySQL C API. In addition, there are a few
  209. additional keywords that correspond to what you would pass
  210. ``mysql_options()`` before connecting. Note that some
  211. parameters must be specified as keyword arguments! The
  212. default value for each parameter is NULL or zero, as
  213. appropriate. Consult the MySQL documentation for more
  214. details. The important parameters are:
  215. host
  216. name of host to connect to. Default: use the local host
  217. via a UNIX socket (where applicable)
  218. user
  219. user to authenticate as. Default: current effective user.
  220. passwd
  221. password to authenticate with. Default: no password.
  222. db
  223. database to use. Default: no default database.
  224. port
  225. TCP port of MySQL server. Default: standard port (3306).
  226. unix_socket
  227. location of UNIX socket. Default: use default location or
  228. TCP for remote hosts.
  229. conv
  230. type conversion dictionary. Default: a copy of
  231. ``MySQLdb.converters.conversions``
  232. compress
  233. Enable protocol compression. Default: no compression.
  234. connect_timeout
  235. Abort if connect is not completed within
  236. given number of seconds. Default: no timeout (?)
  237. named_pipe
  238. Use a named pipe (Windows). Default: don't.
  239. init_command
  240. Initial command to issue to server upon
  241. connection. Default: Nothing.
  242. read_default_file
  243. MySQL configuration file to read; see
  244. the MySQL documentation for ``mysql_options()``.
  245. read_default_group
  246. Default group to read; see the MySQL
  247. documentation for ``mysql_options()``.
  248. cursorclass
  249. cursor class that ``cursor()`` uses, unless
  250. overridden. Default: ``MySQLdb.cursors.Cursor``. *This
  251. must be a keyword parameter.*
  252. use_unicode
  253. If True, CHAR and VARCHAR and TEXT columns are returned as
  254. Unicode strings, using the configured character set. It is
  255. best to set the default encoding in the server
  256. configuration, or client configuration (read with
  257. read_default_file). If you change the character set after
  258. connecting (MySQL-4.1 and later), you'll need to put the
  259. correct character set name in connection.charset.
  260. If False, text-like columns are returned as normal strings,
  261. but you can always write Unicode strings.
  262. *This must be a keyword parameter.*
  263. charset
  264. If present, the connection character set will be changed
  265. to this character set, if they are not equal. Support for
  266. changing the character set requires MySQL-4.1 and later
  267. server; if the server is too old, UnsupportedError will be
  268. raised. This option implies use_unicode=True, but you can
  269. override this with use_unicode=False, though you probably
  270. shouldn't.
  271. If not present, the default character set is used.
  272. *This must be a keyword parameter.*
  273. sql_mode
  274. If present, the session SQL mode will be set to the given
  275. string. For more information on sql_mode, see the MySQL
  276. documentation. Only available for 4.1 and newer servers.
  277. If not present, the session SQL mode will be unchanged.
  278. *This must be a keyword parameter.*
  279. ssl
  280. This parameter takes a dictionary or mapping, where the
  281. keys are parameter names used by the mysql_ssl_set_ MySQL
  282. C API call. If this is set, it initiates an SSL connection
  283. to the server; if there is no SSL support in the client,
  284. an exception is raised. *This must be a keyword
  285. parameter.*
  286. .. _mysql_ssl_set: http://dev.mysql.com/doc/mysql/en/mysql_ssl_set.html
  287. apilevel
  288. String constant stating the supported DB API level. '2.0'
  289. threadsafety
  290. Integer constant stating the level of thread safety the
  291. interface supports. This is set to 1, which means: Threads may
  292. share the module.
  293. The MySQL protocol can not handle multiple threads using the
  294. same connection at once. Some earlier versions of MySQLdb
  295. utilized locking to achieve a threadsafety of 2. While this is
  296. not terribly hard to accomplish using the standard Cursor class
  297. (which uses ``mysql_store_result()``), it is complicated by
  298. SSCursor (which uses ``mysql_use_result()``; with the latter you
  299. must ensure all the rows have been read before another query can
  300. be executed. It is further complicated by the addition of
  301. transactions, since transactions start when a cursor execute a
  302. query, but end when ``COMMIT`` or ``ROLLBACK`` is executed by
  303. the Connection object. Two threads simply cannot share a
  304. connection while a transaction is in progress, in addition to
  305. not being able to share it during query execution. This
  306. excessively complicated the code to the point where it just
  307. isn't worth it.
  308. The general upshot of this is: Don't share connections between
  309. threads. It's really not worth your effort or mine, and in the
  310. end, will probably hurt performance, since the MySQL server runs
  311. a separate thread for each connection. You can certainly do
  312. things like cache connections in a pool, and give those
  313. connections to one thread at a time. If you let two threads use
  314. a connection simultaneously, the MySQL client library will
  315. probably upchuck and die. You have been warned.
  316. For threaded applications, try using a connection pool.
  317. This can be done using the `Pool module`_.
  318. .. _`Pool module`: http://dustman.net/andy/python/Pool
  319. charset
  320. The character set used by the connection. In MySQL-4.1 and newer,
  321. it is possible (but not recommended) to change the connection's
  322. character set with an SQL statement. If you do this, you'll also
  323. need to change this attribute. Otherwise, you'll get encoding
  324. errors.
  325. paramstyle
  326. String constant stating the type of parameter marker formatting
  327. expected by the interface. Set to 'format' = ANSI C printf
  328. format codes, e.g. '...WHERE name=%s'. If a mapping object is
  329. used for conn.execute(), then the interface actually uses
  330. 'pyformat' = Python extended format codes, e.g. '...WHERE
  331. name=%(name)s'. However, the API does not presently allow the
  332. specification of more than one style in paramstyle.
  333. Note that any literal percent signs in the query string passed
  334. to execute() must be escaped, i.e. %%.
  335. Parameter placeholders can **only** be used to insert column
  336. values. They can **not** be used for other parts of SQL, such as
  337. table names, statements, etc.
  338. conv
  339. A dictionary or mapping which controls how types are converted
  340. from MySQL to Python and vice versa.
  341. If the key is a MySQL type (from ``FIELD_TYPE.*``), then the value
  342. can be either:
  343. * a callable object which takes a string argument (the MySQL
  344. value),' returning a Python value
  345. * a sequence of 2-tuples, where the first value is a combination
  346. of flags from ``MySQLdb.constants.FLAG``, and the second value
  347. is a function as above. The sequence is tested until the flags
  348. on the field match those of the first value. If both values
  349. are None, then the default conversion is done. Presently this
  350. is only used to distinquish TEXT and BLOB columns.
  351. If the key is a Python type or class, then the value is a
  352. callable Python object (usually a function) taking two arguments
  353. (value to convert, and the conversion dictionary) which converts
  354. values of this type to a SQL literal string value.
  355. This is initialized with reasonable defaults for most
  356. types. When creating a Connection object, you can pass your own
  357. type converter dictionary as a keyword parameter. Otherwise, it
  358. uses a copy of ``MySQLdb.converters.conversions``. Several
  359. non-standard types are returned as strings, which is how MySQL
  360. returns all columns. For more details, see the built-in module
  361. documentation.
  362. Connection Objects
  363. ..................
  364. Connection objects are returned by the ``connect()`` function.
  365. commit()
  366. If the database and the tables support transactions, this
  367. commits the current transaction; otherwise this method
  368. successfully does nothing.
  369. rollback()
  370. If the database and tables support transactions, this rolls back
  371. (cancels) the current transaction; otherwise a
  372. ``NotSupportedError`` is raised.
  373. cursor([cursorclass])
  374. MySQL does not support cursors; however, cursors are easily
  375. emulated. You can supply an alternative cursor class as an
  376. optional parameter. If this is not present, it defaults to the
  377. value given when creating the connection object, or the standard
  378. ``Cursor`` class. Also see the additional supplied cursor
  379. classes in the usage section.
  380. There are many more methods defined on the connection object which
  381. are MySQL-specific. For more information on them, consult the internal
  382. documentation using ``pydoc``.
  383. Cursor Objects
  384. ..............
  385. callproc(procname, args)
  386. Calls stored procedure procname with the sequence of arguments
  387. in args. Returns the original arguments. Stored procedure
  388. support only works with MySQL-5.0 and newer.
  389. **Compatibility note:** PEP-249_ specifies that if there are
  390. OUT or INOUT parameters, the modified values are to be
  391. returned. This is not consistently possible with MySQL. Stored
  392. procedure arguments must be passed as server variables, and
  393. can only be returned with a SELECT statement. Since a stored
  394. procedure may return zero or more result sets, it is impossible
  395. for MySQLdb to determine if there are result sets to fetch
  396. before the modified parmeters are accessible.
  397. The parameters are stored in the server as @_*procname*_*n*,
  398. where *n* is the position of the parameter. I.e., if you
  399. cursor.callproc('foo', (a, b, c)), the parameters will be
  400. accessible by a SELECT statement as @_foo_0, @_foo_1, and
  401. @_foo_2.
  402. **Compatibility note:** It appears that the mere act of
  403. executing the CALL statement produces an empty result set, which
  404. appears after any result sets which might be generated by the
  405. stored procedure. Thus, you will always need to use nextset() to
  406. advance result sets.
  407. close()
  408. Closes the cursor. Future operations raise ``ProgrammingError``.
  409. If you are using server-side cursors, it is very important to
  410. close the cursor when you are done with it and before creating a
  411. new one.
  412. info()
  413. Returns some information about the last query. Normally
  414. you don't need to check this. If there are any MySQL
  415. warnings, it will cause a Warning to be issued through
  416. the Python warning module. By default, Warning causes a
  417. message to appear on the console. However, it is possible
  418. to filter these out or cause Warning to be raised as exception.
  419. See the MySQL docs for ``mysql_info()``, and the Python warning
  420. module. (Non-standard)
  421. setinputsizes()
  422. Does nothing, successfully.
  423. setoutputsizes()
  424. Does nothing, successfully.
  425. nextset()
  426. Advances the cursor to the next result set, discarding the remaining
  427. rows in the current result set. If there are no additional result
  428. sets, it returns None; otherwise it returns a true value.
  429. Note that MySQL doesn't support multiple result sets until 4.1.
  430. Some examples
  431. .............
  432. The ``connect()`` method works nearly the same as with `_mysql`_::
  433. import MySQLdb
  434. db=MySQLdb.connect(passwd="moonpie",db="thangs")
  435. To perform a query, you first need a cursor, and then you can execute
  436. queries on it::
  437. c=db.cursor()
  438. max_price=5
  439. c.execute("""SELECT spam, eggs, sausage FROM breakfast
  440. WHERE price < %s""", (max_price,))
  441. In this example, ``max_price=5`` Why, then, use ``%s`` in the
  442. string? Because MySQLdb will convert it to a SQL literal value, which
  443. is the string '5'. When it's finished, the query will actually say,
  444. "...WHERE price < 5".
  445. Why the tuple? Because the DB API requires you to pass in any
  446. parameters as a sequence. Due to the design of the parser, (max_price)
  447. is interpreted as using algebraic grouping and simply as max_price and
  448. not a tuple. Adding a comma, i.e. (max_price,) forces it to make a
  449. tuple.
  450. And now, the results::
  451. >>> c.fetchone()
  452. (3L, 2L, 0L)
  453. Quite unlike the ``_mysql`` example, this returns a single tuple,
  454. which is the row, and the values are properly converted by default...
  455. except... What's with the L's?
  456. As mentioned earlier, while MySQL's INTEGER column translates
  457. perfectly into a Python integer, UNSIGNED INTEGER could overflow, so
  458. these values are converted to Python long integers instead.
  459. If you wanted more rows, you could use ``c.fetchmany(n)`` or
  460. ``c.fetchall()``. These do exactly what you think they do. On
  461. ``c.fetchmany(n)``, the ``n`` is optional and defaults to
  462. ``c.arraysize``, which is normally 1. Both of these methods return a
  463. sequence of rows, or an empty sequence if there are no more rows. If
  464. you use a weird cursor class, the rows themselves might not be tuples.
  465. Note that in contrast to the above, ``c.fetchone()`` returns ``None``
  466. when there are no more rows to fetch.
  467. The only other method you are very likely to use is when you have to
  468. do a multi-row insert::
  469. c.executemany(
  470. """INSERT INTO breakfast (name, spam, eggs, sausage, price)
  471. VALUES (%s, %s, %s, %s, %s)""",
  472. [
  473. ("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),
  474. ("Not So Much Spam Plate", 3, 2, 0, 3.95 ),
  475. ("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )
  476. ] )
  477. Here we are inserting three rows of five values. Notice that there is
  478. a mix of types (strings, ints, floats) though we still only use
  479. ``%s``. And also note that we only included format strings for one
  480. row. MySQLdb picks those out and duplicates them for each row.
  481. Using and extending
  482. -------------------
  483. In general, it is probably wise to not directly interact with the DB
  484. API except for small applicatons. Databases, even SQL databases, vary
  485. widely in capabilities and may have non-standard features. The DB API
  486. does a good job of providing a reasonably portable interface but some
  487. methods are non-portable. Specifically, the parameters accepted by
  488. ``connect()`` are completely implementation-dependent.
  489. If you believe your application may need to run on several different
  490. databases, the author recommends the following approach, based on
  491. personal experience: Write a simplified API for your application which
  492. implements the specific queries and operations your application needs
  493. to perform. Implement this API as a base class which should be have
  494. few database dependencies, and then derive a subclass from this which
  495. implements the necessary dependencies. In this way, porting your
  496. application to a new database should be a relatively simple matter of
  497. creating a new subclass, assuming the new database is reasonably
  498. standard.
  499. Because MySQLdb's Connection and Cursor objects are written in Python,
  500. you can easily derive your own subclasses. There are several Cursor
  501. classes in MySQLdb.cursors:
  502. BaseCursor
  503. The base class for Cursor objects. This does not raise Warnings.
  504. CursorStoreResultMixIn
  505. Causes the Cursor to use the ``mysql_store_result()`` function to
  506. get the query result. The entire result set is stored on the
  507. client side.
  508. CursorUseResultMixIn
  509. Causes the cursor to use the ``mysql_use_result()`` function to
  510. get the query result. The result set is stored on the server side
  511. and is transferred row by row using fetch operations.
  512. CursorTupleRowsMixIn
  513. Causes the cursor to return rows as a tuple of the column values.
  514. CursorDictRowsMixIn
  515. Causes the cursor to return rows as a dictionary, where the keys
  516. are column names and the values are column values. Note that if
  517. the column names are not unique, i.e., you are selecting from two
  518. tables that share column names, some of them will be rewritten as
  519. ``table.column``. This can be avoided by using the SQL ``AS``
  520. keyword. (This is yet-another reason not to use ``*`` in SQL
  521. queries, particularly where ``JOIN`` is involved.)
  522. Cursor
  523. The default cursor class. This class is composed of
  524. ``CursorWarningMixIn``, ``CursorStoreResultMixIn``,
  525. ``CursorTupleRowsMixIn,`` and ``BaseCursor``, i.e. it raises
  526. ``Warning``, uses ``mysql_store_result()``, and returns rows as
  527. tuples.
  528. DictCursor
  529. Like ``Cursor`` except it returns rows as dictionaries.
  530. SSCursor
  531. A "server-side" cursor. Like ``Cursor`` but uses
  532. ``CursorUseResultMixIn``. Use only if you are dealing with
  533. potentially large result sets.
  534. SSDictCursor
  535. Like ``SSCursor`` except it returns rows as dictionaries.
  536. Embedded Server
  537. ---------------
  538. Instead of connecting to a stand-alone server over the network,
  539. the embedded server support lets you run a full server right in
  540. your Python code or application server.
  541. If you have built MySQLdb with embedded server support, there
  542. are two additional functions you will need to make use of:
  543. server_init(args, groups)
  544. Initialize embedded server. If this client is not linked against
  545. the embedded server library, this function does nothing.
  546. args
  547. sequence of command-line arguments
  548. groups
  549. sequence of groups to use in defaults files
  550. server_end()
  551. Shut down embedded server. If not using an embedded server, this
  552. does nothing.
  553. See the MySQL documentation for more information on the embedded
  554. server.
  555. :Title: MySQLdb: a Python interface for MySQL
  556. :Author: Andy Dustman
  557. :Version: $Revision: 421 $