Python筆記(5)

整理一下接下來IR_Final_project需要用到相關的Mysql資訊

sql:語法

1. connect to Mysql server:

$ mysql -u username -p

2. show databases and tables and use database:

show databases
show tables
use databases

3. select出需要的table column資訊

select * from table_list;
select * from table_list where primary_constraint;
select column_list from table_list;
select column_list from table_list primary_constaint;

4. Python connect to sql server example:

import MySQLdb

#connect database
db = MySQLdb.connect('localhost', 'database', 'test', 'db1')

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print "Database version : %s " % data

#disconnect sql server
db.close()

5. Python fetch table data:
可用的function:參考網站

fetchone(): 
This method fetches the next row of a query result set. A result set is an object that is returned when a cursor object is used to query a table.

fetchall(): 
This method fetches all the rows in a result set. If some rows have already been extracted from the result set, the fetchall() method retrieves the remaining rows from the result set.

rowcount: 
This is a read-only attribute and returns the number of rows that were affected by an execute() method.

example code:

import MySQLdb

#MySQLdb.connect(server, username, password, database_name)
db = MySQLdb.connect('localhost', 'database', 'test', 'db1')

# prepare a cursor object using cursor() method
cursor = db.cursor()

sql_command = "select * from tb"

try:
    #execute sql command
    cursor.execute(sql_command)
    #fetch all raw data
    result = cursor.fetchall()
    for raw in result:
        bang = raw[0]
        uria = raw[1]
        tuki = raw[2]
        print "bang = %s, uria = %s, tuki = %s" % (bang, uria, tuki)
except:
    print "Error: unable to fecth data"

db.close()