如何在Python中连接到MySQL数据库?

How do I connect to a MySQL database using a python program?

如何使用python程序连接到MySQL数据库?

16 个解决方案

#1

Connecting to MYSQL with Python in 3 steps

1 - Setting

1 -设置

You must install a MySQL driver before doing anything. Unlike PHP, only the SQLite driver is installed by default with Python. The most used package to do so is MySQLdb but it's hard to install it using easy_install.

在做任何事情之前，您必须安装一个MySQL驱动程序。与PHP不同，在默认情况下，只有SQLite驱动程序安装了Python。最常用的包是MySQLdb，但是使用easy_install很难安装它。

For Windows user, you can get an exe of MySQLdb.

对于Windows用户，您可以获得MySQLdb的exe。

For Linux, this is a casual package (python-mysqldb). (You can use sudo apt-get install python-mysqldb (for debian based distros), yum install MySQL-python (for rpm-based), or dnf install python-mysql (for modern fedora distro) in command line to download.)

对于Linux，这是一个临时包(python-mysqldb)。(您可以使用sudo apt-get安装python-mysqldb(用于基于debian的发行版)，yum安装MySQL-python(用于基于rpm的)，或者dnf在命令行中安装python-mysql(用于现代fedora发行版)来下载。)

For Mac, you can install MySQLdb using Macport.

对于Mac，可以使用Macport安装MySQLdb。

2 - Usage

2 -使用

After installing, reboot. This is not mandatory, but will prevent me from answering 3 or 4 other questions in this post if something goes wrong. So please reboot.

安装后,重新启动。这不是强制性的，但是如果有问题的话，我将不能回答这篇文章中的3到4个问题。所以请重新启动。

Then it is just like using another package :

就像使用另一个包:

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect(host="localhost",    # your host, usually localhost
                     user="john",         # your username
                     passwd="megajonhy",  # your password
                     db="jonhydb")        # name of the data base

# you must create a Cursor object. It will let
#  you execute all the queries you need
cur = db.cursor()

# Use all the SQL you like
cur.execute("SELECT * FROM YOUR_TABLE_NAME")

# print all the first cell of all the rows
for row in cur.fetchall():
    print row[0]

db.close()

Of course, there are thousand of possibilities and options; this is a very basic example. You will have to look at the documentation. A good starting point.

当然，有上千种可能性和选择;这是一个非常基本的例子。您必须查看文档。一个好的起点。

3 - More advanced usage

3 -更高级的用法

Once you know how it works, you may want to use an ORM to avoid writting SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is SQLAlchemy.

一旦您了解了它的工作原理，您可能希望使用ORM来避免手工编写SQL并操纵表，因为它们是Python对象。Python社区中最著名的ORM是SQLAlchemy。

I strongly advise you to use it: your life is going to be much easier.

我强烈建议你使用它:你的生活将会更容易。

I recently discovered another jewel in the Python world: peewee. It's a very lite ORM, really easy and fast to setup then use. It makes my day for small projects or stand alone apps, where using big tools like SQLAlchemy or Django is overkill :

我最近发现了Python世界中的另一颗宝石:皮尔。这是一个非常简单的ORM，非常容易和快速的设置，然后使用。对于小型项目或独立应用程序来说，使用SQLAlchemy或Django这样的大型工具简直是大材小用:

import peewee
from peewee import *

db = MySQLDatabase('jonhydb', user='john', passwd='megajonhy')

class Book(peewee.Model):
    author = peewee.CharField()
    title = peewee.TextField()

    class Meta:
        database = db

Book.create_table()
book = Book(author="me", title='Peewee is cool')
book.save()
for book in Book.filter(author="me"):
    print book.title

This example works out of the box. Nothing other than having peewee (pip install peewee) is required.

这个示例是开箱即用的。除了有窥视(pip安装窥视)是不需要的。

#2

Here's one way to do it:

有一种方法:

#!/usr/bin/python
import MySQLdb

# Connect
db = MySQLdb.connect(host="localhost",
                     user="appuser",
                     passwd="",
                     db="onco")

cursor = db.cursor()

# Execute SQL select statement
cursor.execute("SELECT * FROM location")

# Commit your changes if writing
# In this case, we are only reading data
# db.commit()

# Get the number of rows in the resultset
numrows = cursor.rowcount

# Get and display one row at a time
for x in range(0, numrows):
    row = cursor.fetchone()
    print row[0], "-->", row[1]

# Close the connection
db.close()

Reference here

参考这里

#3

Oracle (MySQL) now supports a pure Python connector. That means no binaries to install: it's just a Python library. It's called "Connector/Python".

Oracle (MySQL)现在支持纯Python连接器。这意味着不需要安装二进制文件:它只是一个Python库。它叫做“连接器/ Python”。

http://dev.mysql.com/downloads/connector/python/

http://dev.mysql.com/downloads/connector/python/

#4

If you do not need MySQLdb, but would accept any library, I would very, very much recommend MySQL Connector/Python from MySQL: http://dev.mysql.com/downloads/connector/python/.

如果您不需要MySQLdb，但会接受任何库，我将非常、非常推荐MySQL Connector/Python: http://dev.mysql.com/downloads/connector/python/。

It is one package (around 110k), pure Python, so it is system independent, and dead simple to install. You just download, double-click, confirm license agreement and go. There is no need for Xcode, MacPorts, compiling, restarting …

它是一个包(大约110k)，纯Python，所以它是独立于系统的，安装非常简单。您只需下载、双击、确认许可协议并执行。不需要Xcode、MacPorts、编译、重新启动……

Then you connect like:

然后连接:

import mysql.connector    
cnx = mysql.connector.connect(user='scott', password='tiger',
                              host='127.0.0.1',
                              database='employees')

try:
   cursor = cnx.cursor()
   cursor.execute("""
      select 3 from your_table
   """)
   result = cursor.fetchall()
   print result
finally:
    cnx.close()

#5

Stop Using MySQLDb if you want to avoid installing mysql headers just to access mysql from python.

如果您想避免安装mysql标头，而只是为了从python访问mysql，请停止使用MySQLDb。

Use pymysql. It does all of what MySQLDb does, but it was implemented purely in Python with NO External Dependencies. This makes the installation process on all operating systems consistent and easy. pymysql is a drop in replacement for MySQLDb and IMHO there is no reason to ever use MySQLDb for anything... EVER! - PTSD from installing MySQLDb on Mac OSX and *Nix systems, but that's just me.

使用pymysql。它完成了MySQLDb所做的所有工作，但它完全是在没有外部依赖关系的Python中实现的。这使得在所有操作系统上的安装过程一致和容易。pymysql是MySQLDb和IMHO的替代品，没有理由使用MySQLDb做任何事情……!- Mac OSX和*Nix系统上安装MySQLDb导致的创伤后应激障碍，但那只是我。

Installation

安装

pip install pymysql

pip安装pymysql

That's it... you are ready to play.

就是这样……你已经准备好了。

Example usage from pymysql Github repo

示例使用来自pymysql Github repo

import pymysql.cursors
import pymysql

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('[e-mail]', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('[e-mail]',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

ALSO - Replace MySQLdb in existing code quickly and transparently

同时，快速透明地替换现有代码中的MySQLdb。

If you have existing code that uses MySQLdb, you can easily replace it with pymysql using this simple process:

如果您有使用MySQLdb的现有代码，您可以使用这个简单的过程轻松地将其替换为pymysql:

# import MySQLdb << Remove this line and replace with:
import pymysql
pymysql.install_as_MySQLdb()

All subsequent references to MySQLdb will use pymysql transparently.

所有对MySQLdb的后续引用都将透明地使用pymysql。

#6

As a db driver, there is also oursql. Some of the reasons listed on that link, which say why oursql is better:

作为一个db驱动程序，还有我们的sql。链接上列出的一些原因，说明了为什么我们的sql更好:

• oursql has real parameterization, sending the SQL and data to MySQL completely separately.
• oursql具有真正的参数化，将SQL和数据分别发送给MySQL。
• oursql allows text or binary data to be streamed into the database and streamed out of the database, instead of requiring everything to be buffered in the client.
• 我们的sql允许将文本或二进制数据流到数据库中，并从数据库中流出，而不是要求在客户机中缓冲所有内容。
• oursql can both insert rows lazily and fetch rows lazily.
• oursql既可以延迟插入行，也可以延迟获取行。
• oursql has unicode support on by default.
• oursql默认支持unicode。
• oursql supports python 2.4 through 2.7 without any deprecation warnings on 2.6+ (see PEP 218) and without completely failing on 2.7 (see PEP 328).
• oursql支持python 2.4到2.7，在2.6+上没有任何弃用警告(参见PEP 218)，在2.7上也没有完全失败(参见PEP 328)。
• oursql runs natively on python 3.x.
• oursql是在python 3.x上运行的。

So how to connect to mysql with oursql?

Very similar to mysqldb:

mysqldb非常相似:

import oursql

db_connection = oursql.connect(host='127.0.0.1',user='foo',passwd='foobar',db='db_name')
cur=db_connection.cursor()
cur.execute("SELECT * FROM `tbl_name`")
for row in cur.fetchall():
    print row[0]

The tutorial in the documentation is pretty decent.

文档中的教程相当不错。

And of course for ORM SQLAlchemy is a good choice, as already mentioned in the other answers.

当然，ORM SQLAlchemy是一个很好的选择，正如其他答案中已经提到的那样。

#7

Try using MySQLdb

试着用MySQLdb

There is a how to page here: http://www.kitebird.com/articles/pydbapi.html

这里有一个如何页面:http://www.kitebird.com/articles/pydbapi.html

---

From the page:

从页面:

# server_version.py - retrieve and display database server version

import MySQLdb

conn = MySQLdb.connect (host = "localhost",
                        user = "testuser",
                        passwd = "testpass",
                        db = "test")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version:", row[0]
cursor.close ()
conn.close ()

#8

Despite all answers above, in case you do not want to connect to a specific database upfront, for example, if you want to create the database still (!), you can use connection.select_db(database), as demonstrated in the following.

尽管上面有所有的答案，但是如果您不想预先连接到特定的数据库，例如，如果您想要创建仍旧(!)的数据库，您可以使用connector .select_db(数据库)，如下所示。

import pymysql.cursors
connection = pymysql.connect(host='localhost',
                         user='mahdi',
                         password='mahdi',
                         charset='utf8mb4',
                         cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS "+database)
connection.select_db(database)
sql_create = "CREATE TABLE IF NOT EXISTS "+tablename+(timestamp DATETIME NOT NULL PRIMARY KEY)"
cursor.execute(sql_create)
connection.commit()
cursor.close()

#9

MySQLdb is the straightforward way. You get to execute SQL queries over a connection. Period.

MySQLdb是一种简单的方法。您可以在连接上执行SQL查询。时期。

My preferred way, which is also pythonic, is to use the mighty SQLAlchemy instead. Here is a query related tutorial, and here is a tutorial on ORM capabilities of SQLALchemy.

我喜欢的方法，也是python语言的，是使用强大的SQLAlchemy。这里有一个与查询相关的教程，这里有一个关于SQLALchemy的ORM功能的教程。

#10

SqlAlchemy

---

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. SQLAlchemy provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.

SQLAlchemy是Python SQL工具包和对象关系映射器，它为应用程序开发人员提供了SQL的全部功能和灵活性。SQLAlchemy提供了一套众所周知的企业级持久性模式，专为高效和高性能的数据库访问而设计，适用于简单且python化的领域语言。

Installation

pip install sqlalchemy

Usage with RAW query

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

engine = create_engine("mysql://<user_name>:<password>@<host_name>/smsmagic")
session_obj = sessionmaker(bind=engine)
session = scoped_session(session_obj)

# insert into database
session.execute("insert into person values(2, 'random_name')")
session.flush()
session.commit()

Usage with ORM way

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session

Base = declarative_base()
engine = create_engine("mysql://<user_name>:<password>@<host_name>/smsmagic")
session_obj = sessionmaker(bind=engine)
session = scoped_session(session_obj)

# Bind the engine to the metadata of the Base class so that the
# declaratives can be accessed through a DBSession instance
Base.metadata.bind = engine

class Person(Base):
    __tablename__ = 'person'
    # Here we define columns for the table person
    # Notice that each column is also a normal Python instance attribute.
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)

# insert into database
person_obj = Person(id=12, name="name")
session.add(person_obj)
session.flush()
session.commit()

#11

Just a modification in above answer. Simply run this command to install mysql for python

只是上面答案的一个修改。只需运行此命令即可为python安装mysql。

sudo yum install MySQL-python
sudo apt-get install MySQL-python

remember! It is case sensitive.

记住!这是区分大小写的。

#12

Also take a look at Storm. It is a simple SQL mapping tool which allows you to easily edit and create SQL entries without writing the queries.

也要看一看风暴。它是一个简单的SQL映射工具，允许您在不编写查询的情况下轻松编辑和创建SQL条目。

Here is a simple example:

这里有一个简单的例子:

from storm.locals import *

# User will be the mapped object; you have to create the table before mapping it
class User(object):
        __storm_table__ = "user" # table name
        ID = Int(primary=True) #field ID
        name= Unicode() # field name

database = create_database("mysql://root:password@localhost:3306/databaseName")
store = Store(database)

user = User()
user.name = u"Mark"

print str(user.ID) # None

store.add(user)  
store.flush() # ID is AUTO_INCREMENT

print str(user.ID) # 1 (ID)

store.commit() # commit all changes to the database

To find and object use:

查找和对象使用:

michael = store.find(User, User.name == u"Michael").one()
print str(user.ID) # 10

Find with primary key:

找到与主键:

print store.get(User, 1).name #Mark

For further information see the tutorial.

有关更多信息，请参阅本教程。

#13

first install the driver

第一次安装驱动程序

pip install MySQL-python   

Then a basic code goes like this:

然后一个基本的代码是这样的:

#!/usr/bin/python
import MySQLdb

try:
    db = MySQLdb.connect(host="localhost",      # db server, can be a remote one 
                     db="mydb"                  # database
                     user="mydb",               # username
                     passwd="mydb123",          # password for this username
                     )        

    # Create a Cursor object
    cur = db.cursor()

    # Create a query string. It can contain variables
    query_string = "SELECT * FROM MY_TABLE"

    # Execute the query
    cur.execute(query_string)

    # Get all the rows present the database
    for each_row in cur.fetchall():
        print each_row

    # Close the connection
    db.close()
except Exception, e:
    print 'Error ', e 

#14

For python 3.3

对于python 3.3

CyMySQL https://github.com/nakagami/CyMySQL

CyMySQL https://github.com/nakagami/CyMySQL

I have pip installed on my windows 7, just pip install cymysql

我在windows 7上安装了pip，安装了cymysql

(you don't need cython) quick and painless

(你不需要cython)快速和无痛。

#15

mysqlclient is the best as others only provide support to specific versions of python

mysqlclient是最好的，因为其他人只支持特定版本的python

 pip install mysqlclient

example code

示例代码

    import mysql.connector
    import _mysql
    db=_mysql.connect("127.0.0.1","root","umer","sys")
    #db=_mysql.connect(host,user,password,db)
    # Example of how to insert new values:
    db.query("""INSERT INTO table1 VALUES ('01', 'myname')""")
    db.store_result()
    db.query("SELECT * FROM new1.table1 ;") 
    #new1 is scheme table1 is table mysql 
    res= db.store_result()
    for i in range(res.num_rows()):
        print(result.fetch_row())

see https://github.com/PyMySQL/mysqlclient-python

参见https://github.com/PyMySQL/mysqlclient-python

#16

First, install python-mysql connector from https://dev.mysql.com/downloads/connector/python/

首先，从https://dev.mysql.com/downloads/connector/python/安装python-mysql连接器

on Python console enter:

在Python控制台输入:

pip install mysql-connector-python-rf
import mysql.connector