本文介绍如何用Python操作MySQL数据库,包括安装python-mysqldb
,连接数据库。(单纯用SQL语言,处理变量相当麻烦)
1. Database connector
1.1 安装database connector
正如Java用JDBC连接数据库一样,Python用MySQLdb连接数据库。用如下命令安装python-mysqldb
:
sudo apt-get install python-mysqldb
1.2 连接MySQL
#!/usr/bin/env python import MySQLdb import codecs # for charset utf-8 # Step 1: connect to MySQL db = MySQLdb.connect(host="localhost", user="root", passwd="Jelline", # password db="gtfs") # name of the data base # Step 2: create a Cursor object to execute sql queries cur = db.cursor() # Step 3: execute queries cur.execute("SELECT * FROM stops ORDER BY stop_id") # Step 4: process or print the results with open(filename, 'w') as fp : # fp is a file object # with codecs.open(filename, 'w', encoding='utf-8') as fp : # fp is a file object for row in cur.fetchall() : # cur.fetchall() retunrs a tuple of tuples s = '\t'.join(str(item) for item in row) fp.write(s + '\n') fp.close() # Step 5: clean up cur.close() db.close()
2. 基本用法
快速浏览了Python MySQL Documentation,做个简单笔记,如下:
## execute queries songs = ('Purple Haze', 'All Along the Watch Tower', 'Foxy Lady') for song in songs: cur.execute("INSERT INTO song (title) VALUES (%s)", song) # Substitution Query cur.execute("SELECT * FROM song WHERE id = %s or id = %s", (1,2)) # use a tuple to enclose all multiple parameters to substitue ## print results rows = cur.fetchall() #fetch all rows, return tuple cur.fetchone() #fetch one row ## Cursor attributes print cur.description print cur.rowcount # the number of rows ## Exceptions & Errors try: cur.execute("SELECT * FROM song") rows = cur.fetchall() except MySQLdb.Error, e: try: print "MySQL Error [%d]: %s" % (e.args[0], e.args[1]) except IndexError: print "MySQL Error: %s" % str(e)
关于Curson的属性和方法,可参见PEP 0249 — Python Database API Specification v2.0: Cursor attributes, Cursor methods。
3. ORM
面向对象是从软件工程基本原则(如耦合、聚合、封装)的基础上发展起来的,而关系数据库则是从数学理论发展而来的,两套理论存在显著的区别。为了解决这个不匹配的现象,ORM技术应运而生。ORM(Object Relational Mapping,对象关系映射),是一种程序设计技术,用于实现面向对象编程语言里不同类型系统的数据之间的转换[1]。
从StackOverflow了解到两款ORM:SQLAlchemy和peewee,前者功能强大,后者轻量级。
SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.
Peewee is a simple and small ORM for the Python programming language. It provides a lightweight, expressive Python API for interacting with relational databases
参考资料:
[1]维基百科词条:对象关系映射
[2]StackOverflow: How do I connect to a MySQL Database in Python?
[3]Python MySQL Documentation
[4]Wikipiedia: SQLAlchemy, Peewee ORM
微信赞赏
支付宝赞赏
Pingback: 数据分析:目录 – Spark & Shine
Pingback: GTFS Application Notes: Table of ContentsSpark & Shine | Spark & Shine
Pingback: MySQL使用笔记:目录Spark & Shine | Spark & Shine