Python 数据库操作

我相信人生是值得活的,尽管人在一生中必须遭受痛苦,卑劣,残酷,不幸和死亡的折磨,我依然深信如此.但我认为人生不一定要有意义,只是对一些人而言,他们可以使人生有意义. ---J 赫胥黎

打算把python连接db的例子都总结下来.

python连接sqllite

#!/usr/bin/python
# -*- coding: utf-8 -*-
'''
Created on 2/13/2011

@author: yunshichen@gmail.com
'''

'''

tutorial link: http://docs.python.org/py3k/library/sqlite3.html

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.

sqlite3 was written by Gerhard Häring and provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249.

To use the module, you must first create a Connection object that represents the database. Here the data will be stored in the /tmp/example file:

'''

import sqlite3

DB_FILE = '/tmp/example'

def listData():
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()

    print "Lists data

.\n"
    sql = "select * from stocks order by price"
    c.execute(sql)
    for row in c:
        print(row)

    conn.commit()
    c.close()


def clearTableData():
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()

    sql = "delete from stocks"
    c.execute(sql)

    print "All data are deleted

.\n"
    conn.commit()
    c.close()

def insertData():
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()

    insertData = "insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)"
    c.execute(insertData)

    # Never do this -- insecure!
    #    symbol = 'IBM'
    #    c.execute("

where symbol = '%s'" % symbol)
    # Do this instead
    #    t = (symbol,)
    #    c.execute('select * from stocks where symbol=?', t)

    insertSql = 'insert into stocks values (?,?,?,?,?)'
    for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
              ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
              ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
             ]:
        c.execute(insertSql, t)

    print "Set data into table stocks

.\n"
    conn.commit()
    c.close()

def initTable():
    conn = sqlite3.connect(DB_FILE)
    c = conn.cursor()

    createTable = "create table stocks (date text, trans text, symbol text, qty real, price real)"
    c.execute(createTable)
    print "Created table stocks

.\n"

    conn.commit()
    c.close()


'''
    运行例子:   ./sqllitetest.py -c init
'''
if __name__ =="__main__":

    from optparse import OptionParser
    parser = OptionParser()

    parser.add_option("-c", "--command", action="store",
                  dest="cmd",
                  default="init",
                  type="string",
                  help="Runs functions based on commands. Available commands are init,list,data,deleteAll ")

    options, args = parser.parse_args()

    cmd = options.cmd

    if(cmd=="init"):
        initTable()

    if(cmd=="deleteAll"):
        clearTableData()

    if(cmd=="data"):
        insertData()

    if(cmd=="list"):
        listData()

posted on 2011-02-14 00:02 Chenyunshi 阅读(928) 评论(0) 编辑收藏引用所属分类: Python2.5/2.6

只有注册用户登录后才能发表评论。

yunshichen

Python 数据库操作

导航

统计

常用链接

留言簿(7)

随笔分类

随笔档案

文章分类

相册

搜索

最新评论

阅读排行榜

评论排行榜