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

2021-11-20 00:00:00 python mysql

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

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

推荐答案

三步用Python 2连接MYSQL

1 - 设置

您必须先安装 MySQL 驱动程序，然后才能进行任何操作.与 PHP 不同的是，Python 默认仅安装 SQLite 驱动程序.最常用的包是MySQLdb，但使用easy_install安装它很困难.请注意 MySQLdb 仅支持 Python 2.

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. Please note MySQLdb only supports Python 2.

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

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

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

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.)

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

For Mac, you can install MySQLdb using Macport.

2 - 用法

安装后，重新启动.这不是强制性的，但如果出现问题，它会阻止我在这篇文章中回答 3 或 4 个其他问题.所以请重启.

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

然后就像使用任何其他包一样:

Then it is just like using any other 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 - 更高级的用法

一旦你知道它是如何工作的，你可能想要使用 ORM 来避免手动编写 SQL 并像操作 Python 对象一样操作您的表.Python 社区中最著名的 ORM 是 SQLAlchemy.

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

我强烈建议您使用它:您的生活会轻松得多.

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

我最近发现了 Python 世界中的另一颗宝石:peewee.这是一个非常精简的 ORM，设置和使用非常简单快捷.它让我在小型项目或独立应用程序中度过了一天，在使用 SQLAlchemy 或 Django 等大型工具的情况下是过度的:

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 :

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

这个例子开箱即用.除了需要 peewee (pip install peewee) 外，什么都不需要.

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

相关文章