原网页被墙，放这里备用。
MySQLdb User's Guide
Introduction
MySQLdb is an thread-compatible interface to the popular MySQL database server that provides the Python database API.
Installation
The README file has complete installation instructions.
_mysql
If you want to write applications which are portable across databases, use MySQLdb, and avoid using this module directly. _mysql provides an interface which mostly implements the MySQL C API. For more information, see the MySQL documentation. The documentation for this module is intentionally weak because you probably should use the higher-level MySQLdb module. If you really need it, use the standard MySQL docs and transliterate as necessary.
MySQL C API translation
The MySQL C API has been wrapped in an object-oriented way. The only MySQL data structures which are implemented are the MYSQL (database connection handle) and MYSQL_RES (result handle) types. In general, any function which takes MYSQL *mysql as an argument is now a method of the connection object, and any function which takes MYSQL_RES *result as an argument is a method of the result object. Functions requiring none of the MySQL data structures are implemented as functions in the module. Functions requiring one of the other MySQL data structures are generally not implemented. Deprecated functions are not implemented. In all cases, the mysql_ prefix is dropped from the name. Most of the conn methods listed are also available as MySQLdb Connection object methods. Their use is non-portable.
MySQL C API function mapping
| mysql_affected_rows() | conn.affected_rows() |
| mysql_autocommit() | conn.autocommit() |
| mysql_character_set_name() | conn.character_set_name() |
| mysql_close() | conn.close() |
| mysql_commit() | conn.commit() |
| mysql_connect() | _mysql.connect() |
| mysql_data_seek() | result.data_seek() |
| mysql_debug() | _mysql.debug() |
| mysql_dump_debug_info | conn.dump_debug_info() |
| mysql_escape_string() | _mysql.escape_string() |
| mysql_fetch_row() | result.fetch_row() |
| mysql_get_character_set_info() | conn.get_character_set_info() |
| mysql_get_client_info() | _mysql.get_client_info() |
| mysql_get_host_info() | conn.get_host_info() |
| mysql_get_proto_info() | conn.get_proto_info() |
| mysql_get_server_info() | conn.get_server_info() |
| mysql_info() | conn.info() |
| mysql_insert_id() | conn.insert_id() |
| mysql_num_fields() | result.num_fields() |
| mysql_num_rows() | result.num_rows() |
| mysql_options() | various options to _mysql.connect() |
| mysql_ping() | conn.ping() |
| mysql_query() | conn.query() |
| mysql_real_connect() | _mysql.connect() |
| mysql_real_query() | conn.query() |
| mysql_real_escape_string() | conn.escape_string() |
| mysql_rollback() | conn.rollback() |
| mysql_row_seek() | result.row_seek() |
| mysql_row_tell() | result.row_tell() |
| mysql_select_db() | conn.select_db() |
| mysql_set_character_set() | conn.set_character_set() |
| mysql_ssl_set() | ssl option to _mysql.connect() |
| mysql_stat() | conn.stat() |
| mysql_store_result() | conn.store_result() |
| mysql_thread_id() | conn.thread_id() |
| mysql_thread_safe_client() | conn.thread_safe_client() |
| mysql_use_result() | conn.use_result() |
| mysql_warning_count() | conn.warning_count() |
| CLIENT_* | MySQLdb.constants.CLIENT.* |
| CR_* | MySQLdb.constants.CR.* |
| ER_* | MySQLdb.constants.ER.* |
| FIELD_TYPE_* | MySQLdb.constants.FIELD_TYPE.* |
| FLAG_* | MySQLdb.constants.FLAG.* |
Some _mysql examples
Okay, so you want to use _mysql anyway. Here are some examples.
The simplest possible database connection is:
import _mysql db=_mysql.connect()
This creates a connection to the MySQL server running on the local machine using the standard UNIX socket (or named pipe on Windows), your login name (from the USER environment variable), no password, and does not USE a database. Chances are you need to supply more information.:
db=_mysql.connect("localhost","joebob","moonpie","thangs")
This creates a connection to the MySQL server running on the local machine via a UNIX socket (or named pipe), the user name "joebob", the password "moonpie", and selects the initial database "thangs".
We haven't even begun to touch upon all the parameters connect() can take. For this reason, I prefer to use keyword parameters:
db=_mysql.connect(host="localhost",user="joebob",
passwd="moonpie",db="thangs")
This does exactly what the last example did, but is arguably easier to read. But since the default host is "localhost", and if your login name really was "joebob", you could shorten it to this:
db=_mysql.connect(passwd="moonpie",db="thangs")
UNIX sockets and named pipes don't work over a network, so if you specify a host other than localhost, TCP will be used, and you can specify an odd port if you need to (the default port is 3306):
db=_mysql.connect(host="outhouse",port=3307,passwd="moonpie",db="thangs")
If you really had to, you could connect to the local host with TCP by specifying the full host name, or 127.0.0.1.
Generally speaking, putting passwords in your code is not such a good idea:
db=_mysql.connect(host="outhouse",db="thangs",read_default_file="~/.my.cnf")
This does what the previous example does, but gets the username and password and other parameters from ~/.my.cnf (UNIX-like systems). Read about option files for more details.
So now you have an open connection as db and want to do a query. Well, there are no cursors in MySQL, and no parameter substitution, so you have to pass a complete query string to db.query():
db.query("""SELECT spam, eggs, sausage FROM breakfast
WHERE price < 5""")
There's no return value from this, but exceptions can be raised. The exceptions are defined in a separate module, _mysql_exceptions, but _mysql exports them. Read DB API specification PEP-249 to find out what they are, or you can use the catch-all MySQLError.
At this point your query has been executed and you need to get the results. You have two options:
r=db.store_result() # ...or... r=db.use_result()
Both methods return a result object. What's the difference? store_result() returns the entire result set to the client immediately. If your result set is really large, this could be a problem. One way around this is to add a LIMIT clause to your query, to limit the number of rows returned. The other is to use use_result(), which keeps the result set in the server and sends it row-by-row when you fetch. This does, however, tie up server resources, and it ties up the connection: You cannot do any more queries until you have fetched all the rows. Generally I recommend using store_result() unless your result set is really huge and you can't use LIMIT for some reason.
Now, for actually getting real results:
>>> r.fetch_row()
(('3','2','0'),)
This might look a little odd. The first thing you should know is, fetch_row() takes some additional parameters. The first one is, how many rows (maxrows) should be returned. By default, it returns one row. It may return fewer rows than you asked for, but never more. If you set maxrows=0, it returns all rows of the result set. If you ever get an empty tuple back, you ran out of rows.
The second parameter (how) tells it how the row should be represented. By default, it is zero which means, return as a tuple. how=1 means, return it as a dictionary, where the keys are the column names, or table.column if there are two columns with the same name (say, from a join). how=2 means the same as how=1 except that the keys are always table.column; this is for compatibility with the old Mysqldb module.
OK, so why did we get a 1-tuple with a tuple inside? Because we implicitly asked for one row, since we didn't specify maxrows.
The other oddity is: Assuming these are numeric columns, why are they returned as strings? Because MySQL returns all data as strings and expects you to convert it yourself. This would be a real pain in the ass, but in fact, _mysql can do this for you. (And MySQLdb does do this for you.) To have automatic type conversion done, you need to create a type converter dictionary, and pass this to connect() as the conv keyword parameter.
The keys of conv should be MySQL column types, which in the C API are FIELD_TYPE_*. You can get these values like this:
from MySQLdb.constants import FIELD_TYPE
By default, any column type that can't be found in conv is returned as a string, which works for a lot of stuff. For our purposes, we probably want this:
my_conv = { FIELD_TYPE.LONG: int }
This means, if it's a FIELD_TYPE_LONG, call the builtin int() function on it. Note that FIELD_TYPE_LONG is an INTEGER column, which corresponds to a C long, which is also the type used for a normal Python integer. But beware: If it's really an UNSIGNED INTEGER column, this could cause overflows. For this reason, MySQLdb actually uses long() to do the conversion. But we'll ignore this potential problem for now.
Then if you use db=_mysql.connect(conv=my_conv...), the results will come back ((3, 2, 0),), which is what you would expect.
MySQLdb
MySQLdb is a thin Python wrapper around _mysql which makes it compatible with the Python DB API interface (version 2). In reality, a fair amount of the code which implements the API is in _mysql for the sake of efficiency.
The DB API specification PEP-249 should be your primary guide for using this module. Only deviations from the spec and other database-dependent things will be documented here.
Functions and attributes
Only a few top-level functions and attributes are defined within MySQLdb.
Constructor for creating a connection to the database. Returns a Connection Object. Parameters are the same as for the MySQL C API. In addition, there are a few additional keywords that correspond to what you would pass mysql_options() before connecting. Note that some parameters must be specified as keyword arguments! The default value for each parameter is NULL or zero, as appropriate. Consult the MySQL documentation for more details. The important parameters are:
If True, CHAR and VARCHAR and TEXT columns are returned as Unicode strings, using the configured character set. It is best to set the default encoding in the server configuration, or client configuration (read with read_default_file). If you change the character set after connecting (MySQL-4.1 and later), you'll need to put the correct character set name in connection.charset.
If False, text-like columns are returned as normal strings, but you can always write Unicode strings.
This must be a keyword parameter.
If present, the connection character set will be changed to this character set, if they are not equal. Support for changing the character set requires MySQL-4.1 and later server; if the server is too old, UnsupportedError will be raised. This option implies use_unicode=True, but you can override this with use_unicode=False, though you probably shouldn't.
If not present, the default character set is used.
This must be a keyword parameter.
If present, the session SQL mode will be set to the given string. For more information on sql_mode, see the MySQL documentation. Only available for 4.1 and newer servers.
If not present, the session SQL mode will be unchanged.
This must be a keyword parameter.
Integer constant stating the level of thread safety the interface supports. This is set to 1, which means: Threads may share the module.
The MySQL protocol can not handle multiple threads using the same connection at once. Some earlier versions of MySQLdb utilized locking to achieve a threadsafety of 2. While this is not terribly hard to accomplish using the standard Cursor class (which uses mysql_store_result()), it is complicated by SSCursor (which uses mysql_use_result(); with the latter you must ensure all the rows have been read before another query can be executed. It is further complicated by the addition of transactions, since transactions start when a cursor execute a query, but end when COMMIT or ROLLBACK is executed by the Connection object. Two threads simply cannot share a connection while a transaction is in progress, in addition to not being able to share it during query execution. This excessively complicated the code to the point where it just isn't worth it.
The general upshot of this is: Don't share connections between threads. It's really not worth your effort or mine, and in the end, will probably hurt performance, since the MySQL server runs a separate thread for each connection. You can certainly do things like cache connections in a pool, and give those connections to one thread at a time. If you let two threads use a connection simultaneously, the MySQL client library will probably upchuck and die. You have been warned.
For threaded applications, try using a connection pool. This can be done using the Pool module.
String constant stating the type of parameter marker formatting expected by the interface. Set to 'format' = ANSI C printf format codes, e.g. '...WHERE name=%s'. If a mapping object is used for conn.execute(), then the interface actually uses 'pyformat' = Python extended format codes, e.g. '...WHERE name=%(name)s'. However, the API does not presently allow the specification of more than one style in paramstyle.
Note that any literal percent signs in the query string passed to execute() must be escaped, i.e. %%.
Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.
A dictionary or mapping which controls how types are converted from MySQL to Python and vice versa.
If the key is a MySQL type (from FIELD_TYPE.*), then the value can be either:
- a callable object which takes a string argument (the MySQL value),' returning a Python value
- a sequence of 2-tuples, where the first value is a combination of flags from MySQLdb.constants.FLAG, and the second value is a function as above. The sequence is tested until the flags on the field match those of the first value. If both values are None, then the default conversion is done. Presently this is only used to distinquish TEXT and BLOB columns.
If the key is a Python type or class, then the value is a callable Python object (usually a function) taking two arguments (value to convert, and the conversion dictionary) which converts values of this type to a SQL literal string value.
This is initialized with reasonable defaults for most types. When creating a Connection object, you can pass your own type converter dictionary as a keyword parameter. Otherwise, it uses a copy of MySQLdb.converters.conversions. Several non-standard types are returned as strings, which is how MySQL returns all columns. For more details, see the built-in module documentation.
Connection Objects
Connection objects are returned by the connect() function.
There are many more methods defined on the connection object which are MySQL-specific. For more information on them, consult the internal documentation using pydoc.
Cursor Objects
Calls stored procedure procname with the sequence of arguments in args. Returns the original arguments. Stored procedure support only works with MySQL-5.0 and newer.
Compatibility note: PEP-249 specifies that if there are OUT or INOUT parameters, the modified values are to be returned. This is not consistently possible with MySQL. Stored procedure arguments must be passed as server variables, and can only be returned with a SELECT statement. Since a stored procedure may return zero or more result sets, it is impossible for MySQLdb to determine if there are result sets to fetch before the modified parmeters are accessible.
The parameters are stored in the server as @_*procname*_*n*, where n is the position of the parameter. I.e., if you cursor.callproc('foo', (a, b, c)), the parameters will be accessible by a SELECT statement as @_foo_0, @_foo_1, and @_foo_2.
Compatibility note: It appears that the mere act of executing the CALL statement produces an empty result set, which appears after any result sets which might be generated by the stored procedure. Thus, you will always need to use nextset() to advance result sets.
Advances the cursor to the next result set, discarding the remaining rows in the current result set. If there are no additional result sets, it returns None; otherwise it returns a true value.
Note that MySQL doesn't support multiple result sets until 4.1.
Some examples
The connect() method works nearly the same as with _mysql:
import MySQLdb db=MySQLdb.connect(passwd="moonpie",db="thangs")
To perform a query, you first need a cursor, and then you can execute queries on it:
c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
WHERE price < %s""", (max_price,))
In this example, max_price=5 Why, then, use %s in the string? Because MySQLdb will convert it to a SQL literal value, which is the string '5'. When it's finished, the query will actually say, "...WHERE price < 5".
Why the tuple? Because the DB API requires you to pass in any parameters as a sequence. Due to the design of the parser, (max_price) is interpreted as using algebraic grouping and simply as max_price and not a tuple. Adding a comma, i.e. (max_price,) forces it to make a tuple.
And now, the results:
>>> c.fetchone() (3L, 2L, 0L)
Quite unlike the _mysql example, this returns a single tuple, which is the row, and the values are properly converted by default... except... What's with the L's?
As mentioned earlier, while MySQL's INTEGER column translates perfectly into a Python integer, UNSIGNED INTEGER could overflow, so these values are converted to Python long integers instead.
If you wanted more rows, you could use c.fetchmany(n) or c.fetchall(). These do exactly what you think they do. On c.fetchmany(n), the n is optional and defaults to c.arraysize, which is normally 1. Both of these methods return a sequence of rows, or an empty sequence if there are no more rows. If you use a weird cursor class, the rows themselves might not be tuples.
Note that in contrast to the above, c.fetchone() returns None when there are no more rows to fetch.
The only other method you are very likely to use is when you have to do a multi-row insert:
c.executemany(
"""INSERT INTO breakfast (name, spam, eggs, sausage, price)
VALUES (%s, %s, %s, %s, %s)""",
[
("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),
("Not So Much Spam Plate", 3, 2, 0, 3.95 ),
("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )
] )
Here we are inserting three rows of five values. Notice that there is a mix of types (strings, ints, floats) though we still only use %s. And also note that we only included format strings for one row. MySQLdb picks those out and duplicates them for each row.
Using and extending
In general, it is probably wise to not directly interact with the DB API except for small applicatons. Databases, even SQL databases, vary widely in capabilities and may have non-standard features. The DB API does a good job of providing a reasonably portable interface but some methods are non-portable. Specifically, the parameters accepted by connect() are completely implementation-dependent.
If you believe your application may need to run on several different databases, the author recommends the following approach, based on personal experience: Write a simplified API for your application which implements the specific queries and operations your application needs to perform. Implement this API as a base class which should be have few database dependencies, and then derive a subclass from this which implements the necessary dependencies. In this way, porting your application to a new database should be a relatively simple matter of creating a new subclass, assuming the new database is reasonably standard.
Because MySQLdb's Connection and Cursor objects are written in Python, you can easily derive your own subclasses. There are several Cursor classes in MySQLdb.cursors:
CursorDictRowsMixIn
Causes the cursor to return rows as a dictionary, where the keys are column names and the values are column values. Note that if the column names are not unique, i.e., you are selecting from two tables that share column names, some of them will be rewritten as table.column. This can be avoided by using the SQL AS keyword. (This is yet-another reason not to use * in SQL queries, particularly where JOIN is involved.)
Embedded Server
Instead of connecting to a stand-alone server over the network, the embedded server support lets you run a full server right in your Python code or application server.
If you have built MySQLdb with embedded server support, there are two additional functions you will need to make use of:
server_init(args, groups) Initialize embedded server. If this client is not linked against the embedded server library, this function does nothing.
args sequence of command-line arguments groups sequence of groups to use in defaults files server_end() Shut down embedded server. If not using an embedded server, this does nothing.
See the MySQL documentation for more information on the embedded server.
| MySQLdb: a Python interface for MySQL |
| Andy Dustman |
| $Revision: 421 $ |
相关推荐
3. `README`或`INSTALL`文件：提供了安装和使用MySQLdb的指南，包括必要的依赖和配置信息。 4. `LICENSE`：包含了软件的许可协议，规定了软件的使用权限和限制。 5. 可能还会有其他辅助文件，如测试脚本、文档等。 ...
`libmmd.dll`通常涉及到MySQL的多线程支持，而`libguide40.dll`可能与MySQL的图形用户界面组件有关，例如ODBC驱动或其他接口的指南。在安装或使用Python的MySQLdb时，这些依赖库有时需要被正确地放置在系统路径中，...
Python中的MySQLdb模块是连接和操作...文档"python操作数据库.doc"应该详细介绍了如何使用这个模块，而"windows"文件可能是与Windows平台相关的安装指南或FAQ。通过学习和实践，你可以熟练掌握Python操作MySQL的技能。
在下载的压缩包中，"认真看安装说明.txt"文件很可能是包含如何在Python 2.6环境中正确安装和使用mysqldb的指南。安装步骤通常包括以下几点： 1. **安装依赖**：在安装MySQLdb之前，确保你的系统上已经安装了MySQL...
在Python编程环境中，MySQLdb模块扮演着至关重要的角色，它为Python程序员提供了与MySQL数据库交互的接口。...希望本文的指南能对你在学习或工作中使用MySQLdb有所帮助，如有任何问题，欢迎留言交流。
4. **setup.py**：Python的安装脚本，用户可以通过运行`python setup.py install`来安装MySQLdb-python。 为了使用MySQLdb-python，首先需要确保已经安装了MySQL服务器，并且Python环境也满足其依赖，如Python的...
- **MySQLdb使用指南(Python数据库操作)**、**Python访问MySQL数据库**、**Python安装MySQLDb模块的种种问题及解决**：这三个资源共同构成了学习Python操作MySQL数据库的完整体系，不仅介绍了基本的操作方法，还解决...
### Python技术数据库驱动选用指南 #### 一、MySQLdb - **特点**：MySQLdb是Python社区中最常用的一个与MySQL数据库交互的模块。它在Python 2.x版本中被广泛采用，具有高度稳定性和较快的速度。作为一个成熟的驱动...
在尝试安装MySQLdb，即Python的MySQL连接器时，通常会使用`pip`这一Python包管理工具。然而，在MAMP环境下，直接运行`pip install mysql-python`（MySQLdb的旧名称）可能会遇到问题。首先，可能会出现找不到`mysql_...
对于数据库操作，Python3提供了多种数据库接口，如SQLite、MySQLdb、psycopg2等，本书可能会介绍如何使用这些接口进行数据库的连接、查询、插入、更新和删除操作。 最后，Python3在科学计算、数据分析和机器学习...
在安装完成后，你可以使用以下代码来测试MySQLdb是否成功安装： ```python import MySQLdb ``` 如果没有导入错误，说明MySQLdb已经安装成功。 要使用Python操作MySQL数据库，首先要进行数据库连接。以下是一个简单...
《Python中文指南》v1.0 是一份专为中文用户设计的Python编程教程，旨在帮助初学者快速理解和掌握Python语言的基础知识以及高级特性。这份压缩包包含了一个完整的Python学习资源，包括基本语法、数据结构、函数、类...
当尝试安装或使用像MySQLdb这样的依赖于本地C库的Python模块时，系统可能需要这个编译器来完成安装过程，因为这些模块在Python解释器中以二进制形式存在，需要对应的编译环境来构建。 在标签“安装python包”中，...
本篇指南将深入探讨 Python 中与数据库交互的基本步骤和关键知识点。 首先，Python 提供了多种数据库操作模块，如 MySQLdb（用于 MySQL）、psycopg2（用于 PostgreSQL）、pymongo（用于 MongoDB）等，这些模块提供...
通常，这样的文本文件会提供更详尽的故障排除指南，包括错误日志分析、环境变量设置、特定问题的解决方案等。 总之，正确安装和配置MySQL对于实现Python与数据库的交互至关重要。在CentOS 7上，我们需要按照特定...
9. **开发文档**：项目提供的开发文档可能包括系统架构图、数据库设计、API接口说明、安装部署指南等内容，帮助用户理解并使用系统。 10. **测试与调试**：为了确保程序的稳定性和正确性，开发者可能编写了单元测试...
MySQL教程(CHM)是一个关于MySQL数据库系统的详细指南，旨在帮助用户深入理解和掌握MySQL的核心功能和技术。这个教程主要分为五个关键的技术领域，涵盖了从基础到高级的各个层面。 1. **安装与配置**： - 安装过程...
"mysqldb"可能指的是与数据库相关的文件或目录，用于存储系统数据和用户信息，如课程、用户资料、报名记录等。 在使用"php+mysql 仿培训通"项目时，开发者需要了解以下知识点： 1. PHP基础：包括语法、函数、类和...