SQL資料庫基本操作，利用python将excel資料寫入資料庫，或從庫中讀取出來

首先介紹一下SQL資料庫的一些基本操作：

1建立 2删除 3寫入 4更新(修改) 5條件選擇

有了以上基本操作，就可以建立并存儲一個簡單的資料庫了。

放出python調用的代碼: 此處是調用dos 操作資料庫 不如下面的簡單

# -*- coding: utf-8 -*-
"""
Created on Mon May  6 09:59:32 2019

@author: wenzhe.tian
"""


import MySQLdb

# 打開資料庫連接配接
db = MySQLdb.connect("localhost", "root", "twz1478963", "TESTDB", charset='utf8' )  
# 使用cursor()方法擷取操作遊标 
cursor = db.cursor()

# 如果資料表已經存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
print(cursor.fetchone())
cursor.execute("SELECT VERSION()")


# 建立資料表SQL語句
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT );"""

cursor.execute(sql)

### %\　替換
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES (%s, %s, %s, %s, %s );" % \
       ("'Mac'", "'Mohan'", 20, "'M'", 9000)
         
         
         
try:
   # 執行sql語句
   cursor.execute(sql)
   print(cursor.fetchone())
   # 送出到資料庫執行
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
   
### %\　替換
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES (%s, %s, %s, %s, %s );" % \
       ("'John'", "'Will'", 24, "'M'", 12000)
         
         
         
try:
   # 執行sql語句
   cursor.execute(sql)
   print(cursor.fetchone())
   # 送出到資料庫執行
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
   
   
sql = "SELECT * FROM EMPLOYEE \
       WHERE first_name like %s" % ("'%h_'");
       
'''
WHERE A and/or B between in(A,B)
% 表示多個字值，_ 下劃線表示一個字元；
M% : 為能配符，正規表達式，表示的意思為模糊查詢資訊為 M 開頭的。
%M% : 表示查詢包含M的所有内容。
%M_ : 表示查詢以M在倒數第二位的所有内容
'''
       
# DELETE FROM EMPLOYEE WHERE AGE <20       
       
try:
   # 執行SQL語句
   cursor.execute(sql)
   # 擷取所有記錄清單
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # 列印結果
      print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fecth data")
   
# 關閉資料庫連接配接
db.close()      

View Code

以上代碼即将SQL語言寫出字元的形式 并調用接口執行操作。

下面放一些存儲excel至建立資料庫的例子作為參考: 此處是調用dataframe的to_sql進行操作 需要注意的是encoding='gbk'的中文轉化問題.

寫入資料庫時 表單名字不需要提前建立。

# -*- coding: utf-8 -*-
"""
Created on Tue May  7 15:40:23 2019

@author: ext.wenzhe.tian
"""


from sqlalchemy import create_engine
import pandas as pd

host = '127.0.0.1'
port= 3306
db = 'beilixinyuan'
user = 'root'
password = 'twz1478963'

engine = create_engine(str(r"mysql+mysqldb://%s:" + '%s' + "@%s/%s?charset=utf8") % (user, password, host, db))



try:
#   df = pd.read_csv(r'D:\2PHEV_v3.csv',encoding='gbk')
#   讀取  
    table='sale_phev'
    sql = "SELECT * FROM "+'%s' %(table)
    df=pd.read_sql(sql,con=engine)
#    寫入
#    df.to_sql('sale_ev', con=engine, if_exists='append', index=False)
except Exception as e:

    print(e.message)
    


# 導出方法2
#'''
#WHERE A and/or B between in(A,B)
#% 表示多個字值，_ 下劃線表示一個字元；
#M% : 為能配符，正規表達式，表示的意思為模糊查詢資訊為 M 開頭的。
#%M% : 表示查詢包含M的所有内容。
#%M_ : 表示查詢以M在倒數第二位的所有内容
#'''
#
#
#
import MySQLdb
import pandas as pd
# 打開資料庫連接配接
db = MySQLdb.connect("localhost", "root", "twz1478963", "beilixinyuan", charset='utf8' )  
# 使用cursor()方法擷取操作遊标 
cursor = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)


sql = "SELECT * FROM sale_ev"
       

# DELETE FROM EMPLOYEE WHERE AGE <20       
       
try:
   # 執行SQL語句
   cursor.execute(sql)
   # 擷取所有記錄清單
   results = cursor.fetchall()
   pd.read_sql(sql,con=engine)
 
except:
   print ("Error: unable to fecth data")
   
h=list(results)
df=pd.DataFrame(h)
del results
del h      

View Code