python连接mysql




我如何连接到Python中的MySQL数据库? (10)

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


如果你想避免安装mysql头文件只是为了从python访问mysql,停止使用MySQLDb。

使用pymysql 。 它完成了MySQLDb所做的所有工作,但它完全是在Python中实现的, 没有外部依赖关系 。 这使得所有操作系统上的安装过程始终如一且简单。 pymysql是替代MySQLDb和恕我直言的下降没有理由曾经使用MySQLDb的任何东西......永远! - PTSD from installing MySQLDb on Mac OSX and *Nix systems ,但这只是我。

安装

pip install pymysql

就是这样......你准备好玩了。

来自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, ('[email protected]', '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, ('[email protected]',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

此外 - 快速,透明地替换现有代码中的MySQLdb

如果您有现有的使用MySQLdb的代码,那么您可以使用以下简单的过程轻松地将其替换为pymysql:

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

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


SQLAlchemy的

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

安装

pip install sqlalchemy

使用RAW查询

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

使用ORM方式

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


mysqlclient是最好的,因为其他人只提供对特定版本python的支持

 pip install mysqlclient

示例代码

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

请参阅https://github.com/PyMySQL/mysqlclient-python


也看看风暴 。 这是一个简单的SQL映射工具,它允许您轻松编辑和创建SQL条目而无需编写查询。

这是一个简单的例子:

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:[email protected]: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

寻找和使用对象:

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

使用主键查找:

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

欲了解更多信息,请参阅tutorial


以下是一种方法:

#!/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()

在这里引用


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

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

记得! 它是区分大小写的。


如果你不需要MySQLdb,但会接受任何库,我会非常非常推荐MySQL的MySQL Connector / Python: http://dev.mysql.com/downloads/connector/python/http://dev.mysql.com/downloads/connector/python/

它是一个包(大约110k),纯Python,因此它是独立于系统的,并且安装简单。 您只需下载,双击,确认许可协议即可。 无需Xcode,MacPorts,编译,重新启动...

然后你连接像:

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

尝试使用MySQLdb

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

来自页面:

# 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 ()

尽管上面提供了所有答案,但如果您不想先连接到特定的数据库,例如,如果您想要创建数据库仍然是(!),则可以使用connection.select_db(database) ,如下所示。

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




mysql