#!/usr/bin/pythonimportMySQLdb
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 rowsfor row in cur.fetchall():print row[0]
db.close()
import peewee
from peewee import*
db =MySQLDatabase('jonhydb', user='john', passwd='megajonhy')classBook(peewee.Model):
author = peewee.CharField()
title = peewee.TextField()classMeta:
database = db
Book.create_table()
book =Book(author="me", title='Peewee is cool')
book.save()for book inBook.filter(author="me"):print book.title
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.
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.)
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 – More advanced usage
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.
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
This example works out of the box. Nothing other than having peewee (pip install peewee) is required.
#!/usr/bin/pythonimportMySQLdb# 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 timefor x in range(0, numrows):
row = cursor.fetchone()print row[0],"-->", row[1]# Close the connection
db.close()
Here’s one way to do it, using MySQLdb, which only supports Python 2:
#!/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()
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 …
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()
回答 4
如果要避免安装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仓库的示例用法
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,('webmaster@python.org','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,('webmaster@python.org',))
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()
Stop Using MySQLDb if you want to avoid installing mysql headers just to access mysql from python.
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.
Installation
pip install pymysql
That’s it… you are ready to play.
Example usage from 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, ('webmaster@python.org', '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, ('webmaster@python.org',))
result = cursor.fetchone()
print(result)
finally:
connection.close()
ALSO – Replace MySQLdb in existing code quickly and transparently
If you have existing code that uses MySQLdb, you can easily replace it with pymysql using this simple process:
# import MySQLdb << Remove this line and replace with:
import pymysql
pymysql.install_as_MySQLdb()
All subsequent references to MySQLdb will use pymysql transparently.
As a db driver, there is also oursql. Some of the reasons listed on that link, which say why oursql is better:
oursql has real parameterization, sending the SQL and data to MySQL completely separately.
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.
oursql can both insert rows lazily and fetch rows lazily.
oursql has unicode support on by default.
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 runs natively on python 3.x.
So how to connect to mysql with oursql?
Very similar to 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]
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
engine = create_engine("mysql://<user_name>:<password>@<host_name>/<db_name>")
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 importColumn,Integer,Stringfrom 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>/<db_name>")
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 instanceBase.metadata.bind = engine
classPerson(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()
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.
Installation
pip install sqlalchemy
RAW query
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
engine = create_engine("mysql://<user_name>:<password>@<host_name>/<db_name>")
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 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>/<db_name>")
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()
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.
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()
MySQLdb is the straightforward way. You get to execute SQL queries over a connection. Period.
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.
Best way to connect to MySQL from python is to Use MySQL Connector/Python because it is official Oracle driver for MySQL for working with Python and it works with both Python 3 and Python 2.
Use connect() method of mysql connector python to connect to MySQL.pass the required argument to connect() method. i.e. Host, username, password, and database name.
Create cursor object from connection object returned by connect()method to execute SQL queries.
close the connection after your work completes.
Example:
import mysql.connector
from mysql.connector import Error
try:
conn = mysql.connector.connect(host='hostname',
database='db',
user='root',
password='passcode')
if conn.is_connected():
cursor = conn.cursor()
cursor.execute("select database();")
record = cursor.fetchall()
print ("You're connected to - ", record)
except Error as e :
print ("Print your error msg", e)
finally:
#closing database connection.
if(conn.is_connected()):
cursor.close()
conn.close()
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())
mysqlclient is the best as others only provide support to specific versions of 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())
Even though some of you may mark this as a duplicate and get upset that I am copying someone else’s answer, I would REALLY like to highlight an aspect of Mr. Napik’s response. Because I missed this, I caused nationwide website downtime (9min). If only someone shared this information, I could have prevented it!
The important thing here is the Try and Finally clause. This allows connections to ALWAYS be closed, regardless of what happens in the cursor/sqlstatement portion of the code. A lot of active connections cause DBLoadNoCPU to spike and could crash a db server.
I hope this warning helps to save servers and ultimately jobs! :D
from storm.locals import*# User will be the mapped object; you have to create the table before mapping itclassUser(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_INCREMENTprint 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
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.
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
from flask import Flask, render_template, request
from flask_mysqldb import MySQL
app = Flask(__name__)
app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'root'
app.config['MYSQL_DB'] = 'MyDB'
mysql = MySQL(app)
@app.route('/', methods=['GET', 'POST'])
def index():
if request.method == "POST":
details = request.form
cur = mysql.connection.cursor()
cur.execute ("_Your query_")
mysql.connection.commit()
cur.close()
return 'success'
return render_template('index.html')
if __name__ == '__main__':
app.run()
回答 18
首先安装驱动程序
pip install MySQL-python
然后,基本代码如下所示:
#!/usr/bin/pythonimportMySQLdbtry:
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 databasefor each_row in cur.fetchall():print each_row
# Close the connection
db.close()exceptException, e:print'Error ', e
#!/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
回答 19
您可以通过这种方式将python代码连接到mysql。
importMySQLdb
db =MySQLdb.connect(host="localhost",
user="appuser",
passwd="",
db="onco")
cursor = db.cursor()