Python DB operation

標籤：option   state   目錄   enter   否則   hat   bin   orm   儲存   

 mysql

http://www.cnblogs.com/zhangzhu/archive/2013/07/04/3172486.html

 

1、串連到本機上的MYSQL。
首先開啟DOS視窗，然後進入目錄mysql\bin，再鍵入命令mysql -u root -p，斷行符號後提示你輸密碼.注意使用者名稱前可以有空格也可以沒有空格，但是密碼前必須沒有空格，否則讓你重新輸入密碼。

 

例1：建立一個名為xhkdb的資料庫
   mysql> create database xhkdb;

 

 

py-mysql

http://www.runoob.com/python/python-mysql.html

python操作mysql資料庫

Python 標準資料庫介面為 Python DB-API，Python DB-API為開發人員提供了資料庫應用編程介面。

Python 資料庫介面支援非常多的資料庫，你可以選擇適合你項目的資料庫：

Python DB-API使用流程：

• 引入 API 模組。
• 擷取與資料庫的串連。
• 執行SQL語句和預存程序。
• 關閉資料庫連接。

#!/usr/bin/python# -*- coding: UTF-8 -*-import MySQLdb# 開啟資料庫連接db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset=‘utf8‘ )# 使用cursor()方法擷取操作遊標 cursor = db.cursor()# 使用execute方法執行SQL語句cursor.execute("SELECT VERSION()")# 使用 fetchone() 方法擷取一條資料data = cursor.fetchone()print "Database version : %s " % data# 關閉資料庫連接db.close()

 

flask sqlalchemy orm

http://www.sqlalchemy.org/

The Python SQL Toolkit and Object Relational Mapper

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

It 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.

 

SQLALCHEMY‘S PHILOSOPHY

SQL databases behave less like object collections the more size and performance start to matter; object collections behave less like tables and rows the more abstraction starts to matter. SQLAlchemy aims to accommodate both of these principles.

SQLAlchemy considers the database to be a relational algebra engine, not just a collection of tables. Rows can be selected from not only tables but also joins and other select statements; any of these units can be composed into a larger structure. SQLAlchemy‘s expression language builds on this concept from its core.

SQLAlchemy is most famous for its object-relational mapper (ORM), an optional component that provides the data mapper pattern, where classes can be mapped to the database in open ended, multiple ways - allowing the object model and database schema to develop in a cleanly decoupled way from the beginning.

SQLAlchemy‘s overall approach to these problems is entirely different from that of most other SQL / ORM tools, rooted in a so-called complimentarity- oriented approach; instead of hiding away SQL and object relational details behind a wall of automation, all processes are fully exposed within a series of composable, transparent tools. The library takes on the job of automating redundant tasks while the developer remains in control of how the database is organized and how SQL is constructed.

The main goal of SQLAlchemy is to change the way you think about databases and SQL!

Read some key features of SQLAlchemy, as well as what people are saying about SQLAlchemy.

 

 

例子

http://docs.jinkan.org/docs/flask-sqlalchemy/quickstart.html

 

from flask import Flaskfrom flask.ext.sqlalchemy import SQLAlchemyapp = Flask(__name__)app.config[‘SQLALCHEMY_DATABASE_URI‘] = ‘sqlite:////tmp/test.db‘db = SQLAlchemy(app)class User(db.Model):    id = db.Column(db.Integer, primary_key=True)    username = db.Column(db.String(80), unique=True)    email = db.Column(db.String(120), unique=True)    def __init__(self, username, email):        self.username = username        self.email = email    def __repr__(self):        return ‘<User %r>‘ % self.username

 

>>> from yourapplication import db>>> db.create_all()

砰，你的資料庫就好了。現在來建立一些使用者:

>>> from yourapplication import User>>> admin = User(‘admin‘, ‘[email protected]‘)>>> guest = User(‘guest‘, ‘[email protected]‘)

但是它們還不在資料庫中，所以讓我們確保它們被添加進去:

>>> db.session.add(admin)>>> db.session.add(guest)>>> db.session.commit()

訪問資料庫中的內容易如反掌:

>>> users = User.query.all()[<User u‘admin‘>, <User u‘guest‘>]>>> admin = User.query.filter_by(username=‘admin‘).first()<User u‘admin‘>

 

 

參考：

http://www.pythondoc.com/flask-sqlalchemy/config.html

https://stackoverflow.com/questions/33738467/how-do-i-know-if-i-can-disable-sqlalchemy-track-modifications

  

Python DB operation