【转载】python链接mysql

1。在http://sourceforge.net/projects/mysql-python/下载，连接所需要的包，MysqlDB。最新的版本是1.2.1_p2(for *NIX)或者1.2.0(for i386)，我用的是i386版的，以下都以1.2.0为例。

2。下载后安装，会自动找到你Python的安装目录，装在%Python_HOME%Libsite-packages目录中。

3。代码实例

from MySQLdb import *
def conn():
cn=Connection('127.0.0.1','root','','test')
#Connection()函数的参数依次为
# host(string, host to connect);
# user(string, user to connect as);
# passwd(string, password to use);
# db(string, database to use)
#也可以这样选择数据库
#cn.select_db('test')
cur=cn.cursor()
cur.execute('select * from user')
#设置游标的位置，不设置默认为0
#cur.scroll(0)
row=cur.fetchone()
#查询游标位置的一条记录，返回值为元组
print row[0] #输出第一个字段内容
print row[1]

if __name__=='__main__':
conn()
4。查询时也可执行fetchall()，返回所有记录为一个元组(tuple)，元组的每个元素为每行记录；还有fetchmany(size)

5。增删改的例子
insert:

cur.execute('insert into user (name,passwd) values('sparezgw','asdfasdf')')
cur.insert_id()
update:

cur.execute('update user set passwd='asdfasdf' where name='sparezgw'')
delete:

cur.execute('delete from user where id=2')

------------------------------------------------------------------------------------------------------


MySQL-Python -- MySQL 的 Python 接口2006年07月29日 星期六 上午 00:16
The Python Database API (DBAPI) 2.0http://wiki.woodpecker.org.cn/moin/PyDBAPI2MySQL 的 Python 接口http://sourceforge.net/projects/mysql-pythonimport MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("SELECT * FROM animals")# One By One
# get the number of rows in the resultset
numrows = int(cursor.rowcount)
# get and display one row at a time
for x in range(0,numrows):
row = cursor.fetchone()
print row[0], "-->", row[1]# limit the resultset to 3 items
result = cursor.fetchmany(3)
# iterate through resultset
for record in result:
print record[0] , "-->", record[1]# get the resultset as a tuple
result = cursor.fetchall()
# iterate through resultset
for record in result:
print record[0] , "-->", record[1]# import MySQL module
import MySQLdb
# get user input
name = raw_input("Please enter a name: ")
species = raw_input("Please enter a species: ")
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("INSERT INTO animals (name, species) VALUES (%s, %s)",(name, species))
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("""INSERT INTO test (field1, field2) VALUES ("val1","val2")""")
# get ID of last inserted record
print "ID of inserted record is ", int(cursor.insert_id())
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",db="db56a")
# create a cursor
cursor = db.cursor()
# dynamically generate SQL statements from list
cursor.executemany("INSERT INTO animals (name, species) VALUES (%s,%s)", [('Rollo', 'Rat'), ('Dudley', 'Dolphin'), ('Mark', 'Marmoset')])# import MySQL module
import MySQLdb
# initialize some variables
name = ""
data = []
# loop and ask for user input
while (1):
name = raw_input("Please enter a name (EOF to end): ")
if name == "EOF":
break
species = raw_input("Please enter a species: ")
# put user input into a tuple
tuple = (name, species)
# and append to data[] list
data.append(tuple)
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",db="db56a")
# create a cursor
cursor = db.cursor()
# dynamically generate SQL statements from data[] list
cursor.executemany("INSERT INTO animals (name, species) VALUES (%s,%s)",data)

------------------------------------------------------------------------------------------------------

python连接mysql数据库2008-08-22 17:59
看自己的机器有没有python

#python -v //会进入python

Python 2.4.3 (#1, Dec 11 2006, 11:38:52)
[GCC 4.1.1 20061130 (Red Hat 4.1.1-43)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> //想要退出python 按键Ctrl+D

pythontest。py文件内容

#!/usr/bin/python
# imoprt MySQL module
import MySQLdb
#connect to the database
db=MySQLdb.connect(host="127.0.0.1",user="root",passwd="666666",db="cityman")
#get a cursor
cursor=db.cursor()
#sql statement
cursor.execute("insert into person values('','wangzhi','25','male')")
cursor.execute("select * from person")
#get the result set
result=cursor.fetchall()
#iterate thtough the result set
for i in result:
print(i)
cursor.close

回到终端

# python python2mysql.py
python2mysql.py:9: Warning: Out of range value adjusted for column 'id' at row 1
cursor.execute("insert into person values('','wangzhi','25','male')")
(1L, 'liujun', 28L, 'male')
(2L, 'shenyang', 22L, 'fmale')
(3L, 'lizhiwei', 32L, 'male')
(4L, 'wangzhi', 25L, 'male')

执行成功

perl和python非常相像，这是我见过的最简练的两种语言，真的很棒。


----------------------------------------------------------------------------------------------------------------------

python操作mysql2008-07-08 07:49#!/usr/bin/env python
# -*-coding:UTF-8-*-#这一句告诉python用UTF-8编码
#=========================================================================
#
# NAME: Python MySQL test
#
# AUTHOR: benyur
# DATE : 2004-12-28
#
# COMMENT: 这是一个python连接mysql的例子
#
#=========================================================================

"""
***** This is a MySQL test *****

select:
conn=Connection()
conn.select_db('test')
cur=conn.cursor()
cur.execute('select * from user')
cur.scroll(0)
row1=cur.fetchone()
row1[0]
row1[1]
row1[2]

insert:
cur.execute('insert into user (name,passwd) values(\'benyur\',\'12345\')')
cur.insert_id()

update:
cur.execute('update user set passwd=\'123456\' where name=\'benyur\'')

delete:
cur.execute('delete from user where id=2')

**********************************
"""


from MySQLdb import *


def conn():
conn=Connection()
conn.select_db('test')
cur=conn.cursor()
cur.execute('select * from user')
cur.scroll(0)
row1=cur.fetchone()
row1[0]
row1[1]
row1[2]


def usage():
print __doc__


if __name__=='__main__':
usage()


MySQLdb下载地址：http://sourceforge.net/projects/mysql-python/
下载解压缩后放到%Python_HOME%\Lib\site-packages目录中，python会自动找到此包。
MySQLdb基本上是MySQL C API的Python版，遵循Python Database API Specification v2.0。
其他：

1. 平台及版本
linux 内核2.6，gcc 3.4.4，glibc 2.4
python 2.4.3
mysql 5.0.19
mysql-python 1.2.1-p2
2. 安装mysql-python
tar xvfz MySQL-python-1.2.1_p2.tar.gz
cd MySQL-python-1.2.1_p2
python setup.py build
python setup.py install

3. 使用
import MySQLdb
3.1. 连接
conn = MySQLdb.Connection(host, user, password, dbname)
3.2. 选择数据库
conn.select_db(’database name’)
3.3. 获得cursor
cur = conn.cursor()
3.4. cursor位置设定
cur.scroll(int, mode)
mode可为相对位置或者绝对位置，分别为relative和absolute。


3.5. select
cur.execute(‘select clause’)
例如
cur.execute(‘select * from mytable’)


row = cur.fetchall()
或者：
row1 = cur.fetchone()
3.6. insert
cur.execute(‘inset clause’)
例如
cur.execute(‘insert into table (row1, row2) values (\’111\’, \’222\’)’)


conn.commit()


3.7. update
cur.execute(‘update clause’)
例如
cur.execute(“update table set row1 = ‘’ where row2 = ‘row2 ‘ ”)


conn.commit()


3.8. delete
cur.execute(‘delete clause’)
例如
cur.execute(“delete from table where row1 = ‘row1’ ”)


conn.commit()


4. 心得

------------------------------------------------------------------------------------------------

Python mysql 中文乱码2007年09月05日 星期三 21:48import MySQLdb
db_user = "tiger"
db_pw = "tiger"
db = MySQLdb.connect(host="localhost", user=db_user, passwd=db_pw ,db="finaltldw",charset="gb2312")
c = db.cursor()
c.execute("""select id, name from NODES""")
i=0;
for id, name in c.fetchall():
print "%2d %s" % (id, name)
i=i+1
if i==100:
break

返回结果:
-4 TEXT
-3 DATE
-2 INT
-1 STRING
1 TOP
2 教育
3 机构
4 人
5 地区
6 单位
7 科学研究
8 实验室
9 类型
10 学科
11 新闻
12 学科
13 新闻
14 简介
15 通知
16 通知
17 地区
18 省部级实验室
19 国家实验室

如果编码是UTF-8
转载一个解决方案: 其中的use db

Python操作MySQL以及中文乱码的问题 Python操作MySQL需要安装Python-MySQL
可以从网上搜索一下，和一般的Python包一样安装

安装好之后，模块名字叫做MySQLdb ，在Window和Linux环境下都可以使用，试验了一下挺好用，
不过又发现了烦人的乱麻问题，最后用了几个办法，解决了！

我用了下面几个措施，保证MySQL的输出没有乱麻：
1 Python文件设置编码 utf-8 （文件前面加上 #encoding=utf-8)
2 MySQL数据库charset=utf-8
3 Python连接MySQL是加上参数 charset=utf8
4 设置Python的默认编码为 utf-8 (sys.setdefaultencoding(utf-8)

mysql_test.py
#encoding=utf-8
import sys
import MySQLdb

reload(sys)
sys.setdefaultencoding('utf-8')

db=MySQLdb.connect(user='root',charset='utf8')
cur=db.cursor()
cur.execute('use mydb')
cur.execute('select * from mytb limit 100')

f=file("/home/user/work/tem.txt",'w')

for i in cur.fetchall():
f.write(str(i))
f.write(" ")

f.close()
cur.close()
上面是linux上的脚本,windows下运行正常!

注：MySQL的配置文件设置也必须配置成utf8

设置 MySQL 的 my.cnf 文件，在 [client]/[mysqld]部分都设置默认的字符集（通常在/etc/mysql/my.cnf)：

[client]default-character-set = utf8
[mysqld]default-character-set = utf8