Python *** 作 MySQL 的5种方式

1、MySQLdb

# 前置条件

sudo apt-get install python-dev libmysqlclient-dev # Ubuntu

sudo yum install python-devel mysql-devel # Red Hat / CentOS

# 安装

pip install MySQL-python

Windows 直接通过下载 exe 文件安装

#!/usr/bin/python

import MySQLdb

db = MySQLdb.connect(

host = "localhost", # 主机名

user = "root", # 用户名

passwd = "pythontab.com", # 密码

db = "testdb") # 数据库名称

# 查询前，必须先获取游标

cur = db.cursor()

# 执行的都是原生SQL语句

cur.execute("SELECT * FROM mytable")

for row in cur.fetchall():

print(row[0])

db.close()

2、mysqlclient

# Windows安装

pip install some-package.whl

# linux 前置条件

sudo apt-get install python3-dev # debian / Ubuntu

sudo yum install python3-devel # Red Hat / CentOS

brew install mysql-connector-c # macOS (Homebrew)

pip install mysqlclient

3、PyMySQL

pip install PyMySQL

# 为了兼容mysqldb，只需要加入

pymysql.install_as_MySQLdb()

import pymysql

conn = pymysql.connect(host = '127.0.0.1', user = 'root', passwd = "pythontab.com", db = 'testdb')

cur = conn.cursor()

cur.execute("SELECT Host,User FROM user")

for r in cur:

print(r)

cur.close()

conn.close()

4、peewee

pip install peewee

import peewee

from peewee import *

db = MySQLDatabase('testdb', user = 'root', passwd = 'pythontab.com')

class Book(peewee.Model):

author = peewee.CharField()

title = peewee.TextField()

class Meta:

database = db

Book.create_table()

book = Book(author = "pythontab", title = 'pythontab is good website')

book.save()

for book in Book.filter(author = "pythontab"):

print(book.title)

5、SQLAlchemy

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

from sqlalchemy_declarative import Address, Base, Person

class Address(Base):

__tablename__ = 'address'

id = Column(Integer, primary_key = True)

street_name = Column(String(250))

engine = create_engine('sqlite:///sqlalchemy_example.db')

Base.metadata.bind = engine

DBSession = sessionmaker(bind = engine)

session = DBSession()

# Insert a Person in the person table

new_person = Person(name = 'new person')

session.add(new_person)

session.commit()

python要调用mysql首先要安装python 的mysql模块，centos下可以通过以下方法来安装：

直接yum安装

yum install MySQL-python -y

通过pip安装

pip install mysql-python

查看是否安装成功,运行python，在python终端里面运行

import MySQLdb

如果没有报错说明安装成功，然后就可以连接你的数据了

创建一个数据库的连接

connect = MySQLdb.connect(host='127.0.0.1',user='user',passwd='password',db='db')

2.获取数据库的游标

cor = connect.cursor()

3.执行一个SQL语句

cor.excute("select * from user")

4.获取SQL的结果

for row in cor.fetchall():

print row

5.关闭连接

connect.close()

Python *** 作Mysql或Oracle的流程与JDBC一样，首先创建连接，再获取游标对象，执行 *** 作，获取结果，最后关闭连接。

1、MySQLdb就是Mysql的实现

http://sourceforge.net/projects/mysql-python

yum -y install MySQL-python.x86_64

2、import MySQLdb

3、实例：

#!/usr/bin/python

import os, sys

import MySQLdb

try:

conn MySQLdb.connect(host='localhost',user='root',passwd='123456',db='address')

except Exception,e:

print(e)

sys.exit()

cursor=conn.cursor()

sql='insert into address(name, address) values(%s,%s)'

value=(("zhangsan","haidian"),("lisi","haidian"))

try

cursor.executemany(sql,values)

except Exception,e:

print(e)

sql="select * from address"

cursor.execute(sql)

data=cursor.fetchall()

if data

for x in data:

print(x[0], x[1])

cursor.close()

conn.close()

---