如何使用python程序连接MySQL数据库?
当前回答
再来看看Storm。它是一个简单的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: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
查找和使用对象:
michael = store.find(User, User.name == u"Michael").one()
print str(user.ID) # 10
用主键查找:
print store.get(User, 1).name #Mark
有关更多信息,请参阅教程。
其他回答
再来看看Storm。它是一个简单的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: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
查找和使用对象:
michael = store.find(User, User.name == u"Michael").one()
print str(user.ID) # 10
用主键查找:
print store.get(User, 1).name #Mark
有关更多信息,请参阅教程。
对于Python3.6,我找到了两个驱动程序:pymysql和mysqlclient。我测试了它们之间的性能,得到的结果是:mysqlclient更快。
下面是我的测试过程(需要安装python lib profilehooks来分析时间流逝:
select * from FOO;
立即在mysql终端执行: set中有46410行(0.10秒)
pymysql (2 . 4s):
from profilehooks import profile
import pymysql.cursors
import pymysql
connection = pymysql.connect(host='localhost', user='root', db='foo')
c = connection.cursor()
@profile(immediate=True)
def read_by_pymysql():
c.execute("select * from FOO;")
res = c.fetchall()
read_by_pymysql()
下面是pymysql的配置文件:
mysqlclient 0.4s)
from profilehooks import profile
import MySQLdb
connection = MySQLdb.connect(host='localhost', user='root', db='foo')
c = connection.cursor()
@profile(immediate=True)
def read_by_mysqlclient():
c.execute("select * from FOO;")
res = c.fetchall()
read_by_mysqlclient()
下面是mysqlclient的配置文件:
因此,mysqlclient似乎比pymysql快得多
尝试使用MySQLdb。MySQLdb只支持Python 2。
有一个如何页面在这里: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 ()
首先安装驱动程序(Ubuntu)
Sudo apt-get install python-pip sudo pip install -U pip Sudo apt-get install python-dev libmysqlclient-dev sudo apt-get install MySQL-python
MySQL数据库连接代码
import MySQLdb
conn = MySQLdb.connect (host = "localhost",user = "root",passwd = "pass",db = "dbname")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version:", row[0]
cursor.close ()
conn.close ()
这是Mysql数据库连接
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()
推荐文章
- 证书验证失败:无法获得本地颁发者证书
- 当使用pip3安装包时,“Python中的ssl模块不可用”
- 如何关闭mysql密码验证?
- 无法切换Python与pyenv
- Python if not == vs if !=
- 如何从scikit-learn决策树中提取决策规则?
- 为什么在Mac OS X v10.9 (Mavericks)的终端中apt-get功能不起作用?
- 将旋转的xtick标签与各自的xtick对齐
- 为什么元组可以包含可变项?
- MySQL区分大小写查询
- 如何合并字典的字典?
- 如何创建类属性?
- 不区分大小写的“in”
- 在Python中获取迭代器中的元素个数
- MySQL数据库表中的最大记录数