Python操作数据库

本篇文章涉及内存数据库和mysql数据库，其中操作内存数据用的是python内置的sqlite3模块，操作mysql用的是pymysql（python2.x版本用的是MySQLdb），其中pymysql的安装方式请大家自行百度，此处不做讲解。下面将基本操作代码列出如下：

一、内存数据库

1、利用sqlite3模块创建数据库，并插入数据，查询数据：

'''
利用sqlites模块创建数据库，并插入数据，查询数据
'''
import sqlite3
#创建数据库
con = sqlite3.connect('E:\\studytest\\data\\excel\\data.db')
#创建表语句
query="""CREATE TABLE sales (customer VARCHAR(20),product VARCHAR(40),amount FLOAT,date DATE);"""
#执行语句
con.execute(query)
#提交语句
con.commit()

#在表中插入数据
data = [('Richard Lucas','Notepad',2.50,'2014-01-02'),
        ('Jenny Kim', 'Binder', 4.15, '2014-01-15'),
        ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'),
        ('Steohen Randolph', 'Computer', 679.40, '2014-02-20')]
statement = "INSERT INTO sales VALUES(?,?,?,?)"
#执行多次插入语句
con.executemany(statement,data)
con.commit()

#查询sales表
cursor = con.execute("SELECT * FROM sales")
rows = cursor.fetchall()

#计算查询结果中行的数量
row_counter = 0
for row in rows:
    print(row)
    row_counter += 1
print('Number of rows:%d'%(row_counter))

2、读取csv（EXCEL）文件数据，插入内存数据库

import sqlite3
import csv
import sys
input_file = "E:\\studytest\\data\\csv\\supplier_data.csv"
#创建数据库
con = sqlite3.connect('E:\\studytest\\data\\excel_test\\Suppliers.db')
c = con.cursor()
#创建表语句
creat_table="""CREATE TABLE IF NOT EXISTS Suppliers (Supplier_Name VARCHAR(20),
          Invoice_Number VARCHAR(40),Part_Number VARCHAR(20),Cost FLOAT,Purchase_Date DATE);"""
#执行语句
c.execute(creat_table)
#提交语句
con.commit()

#读取CSV文件数据，并插入到数据库
file_reader = csv.reader(open(input_file,'r'),delimiter=',')
header=next(file_reader,None)
for row in file_reader:
    #封装每行数据
    data = []
    for column_index in range(len(header)):
        data.append(row[column_index])
    print(data)
    c.execute("INSERT INTO Suppliers VALUES(?,?,?,?,?);",data)
con.commit()
print("")
#查询Suppliers表
output = c.execute("SELECT * FROM Suppliers")
rows = output.fetchall()
for row in rows:
    output = []
    for column_index in range(len(row)):
        output.append(row[column_index])
    print(output)

3、更新内存数据库

'''
利用sqlites模块创建数据库，读取CSV文件数据，并插入到创建好的数据库,然后更新已经存在的数据
'''
import sqlite3
import csv
input_file = "E:\\studytest\\data\\database\\data_for_updating.csv"
#创建数据库
con = sqlite3.connect('E:\\studytest\\data\\excel_test\\data.db')
#创建表语句
query="""CREATE TABLE sales (customer VARCHAR(20),product VARCHAR(40),amount FLOAT,date DATE);"""
#执行语句
con.execute(query)
#提交语句
con.commit()

#在表中插入数据
data = [('Richard Lucas','Notepad',2.50,'2014-01-02'),
        ('Jenny Kim', 'Binder', 4.15, '2014-01-15'),
        ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'),
        ('Steohen Randolph', 'Computer', 679.40, '2014-02-20')]
for tuple in data:
    print(tuple)
statement = "INSERT INTO sales VALUES(?,?,?,?)"
#执行多次插入语句
con.executemany(statement,data)
con.commit()
#读取CSV文件并更新特定行
file_reader = csv.reader(open(input_file,'r'),delimiter=',')
header=next(file_reader,None)
for row in file_reader:
    #封装每行数据
    data = []
    for column_index in range(len(header)):
        data.append(row[column_index])
    print(data)
    con.execute("UPDATE sales SET amount=?,date=? WHERE customer=?;",data)
con.commit()
#查询sales表
cursor = con.execute("SELECT * FROM sales");
rows = cursor.fetchall()
for row in rows:
    output = []
    for column_index in range(len(row)):
        output.append(row[column_index])
    print(output)

二、操作MySQL数据库

1、从CSV文件中读取数据存入mysql数据库

'''
从CSV文件读取数据存入mysql
'''
import pymysql
import csv
import sys
from datetime import datetime,date
#CSV文件输入
input_file="E:\\studytest\\data\\csv\\supplier_data.csv"
#连接数据库
con = pymysql.connect(host='127.0.0.1',port=3306,db='my_suppliers',user='root',passwd='666666')
c =con.cursor()
#读取CSV文件数据插入到数据库中
file_reader = csv.reader(open(input_file,'r',newline=''))
header = next(file_reader)
for row in file_reader:
    data= []
    for column_index in range(len(header)):
        if column_index<4:
            data.append(str(row[column_index]).lstrip('$').replace(',','').strip())
        else:
            a_date = datetime.date(datetime.strptime(str(row[column_index]),'%m/%d/%y'))
            a_date = a_date.strftime('%Y-%m-%d')
            data.append(a_date)
    print(data)
    #执行插入语句
    c.execute("""INSERT INTO Suppliers VALUES(%s,%s,%s,%s,%s);""",data)
#提交事务
con.commit()
print("")
#查询Suppliers表
c.execute("SELECT * FROM Suppliers")
rows = c.fetchall()
for row in rows:
    row_list_output = []
    for column_index in range(len(row)):
        row_list_output.append(str(row[column_index]))
    print(row_list_output)

2、从数据库查询数据，并写入CSV文件

'''
查询一个表并将输出写入CSV文件
'''
import csv
import pymysql
#输出文件
output_file="E:\\studytest\\data\\excel_test\\5output.csv"
#连接数据库
con =pymysql.connect(host='127.0.0.1',port=3306,db='my_suppliers',user='root',passwd='666666')
c = con.cursor()
#打开输出文件，写入标题
filewriter=csv.writer(open(output_file,'w',newline=''),delimiter=',')
header=['Supplier Name','Invoice Number','Part Number','Cost','Purchase Date']
filewriter.writerow(header)
#循环数据库，插入数据到输出文件
c.execute("SELECT * FROM Suppliers")
rows = c.fetchall()
for row in rows:
    filewriter.writerow(row)

这一系列文章代码相对较简单，功能比较好理解，主要针对初学者，大神请自行绕过。