使用python和sqlite处理数据

1.处理空行

python程序

import pandas as pd # 导入 pandas库
data = pd.read_excel("PittsburghBridges.xls") # 读取表格数据
res = data.dropna(how="all") # 清除空白行
res.to_excel("PittsburghBridges_new.xls", index=False) # 输出处理后的文件

2.创建一个数据库

使用sqlite 创建一个名为bridgeNew的数据库

CREATE TABLE bridgeNew  
(  
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    bridge_type VARCHAR,   --外键字段
    --创建外键
    CONSTRAINT fk_type  
    FOREIGN KEY (bridge_type)  
    REFERENCES bridge (type)  
);

解释：在 bridgeNew 表中，创建一个外键 bridge_type ，该 bridge_type 字段引用 bridge 表的 type 字段。

3.创建一个数据库

使用 sqlite

CREATE TABLE bridge  
(  
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    type VARCHAR NOT NULL,  
    subtype VARCHAR NOT NULL,  
    pattern VARCHAR NOT NULL,  
    material VARCHAR NOT NULL,  
);

4.使用python创建数据库

1、创建 sqlite数据库，并创建一张表名为 persons 的表，其中包含字段 id 、 name 和 age

import sqlite3 # 导入sqlite
import os
dbPath = 'database.db'
if not os.path.exists(dbPath):
    conn = sqlite3.connect(dbPath)
    c = conn.cursor()
    c.execute('''create table persons (id int primary key not null,name varchar not null,age integer not null);''')
    conn.commit()
    conn.close()

2、写入数据

import sqlite3
dbPath = 'database.db'
conn = sqlite3.connect(dbPath)
c = conn.cursor()
# 插入数据
c.execute('''
insert into persons(id,name,age)
values(1, 'tom', 18)
''')
c.execute('''
insert into persons(id,name,age)
values(2, 'jack', 20)
''')
conn.commit()
print('insert success')
conn.close()

3、查询数据

import sqlite3
#创建数据库连接，返回connection对象
dbPath = 'database.db'
conn = sqlite3.connect(dbPath)
#创建游标对象
cur=conn.cursor()
#查询数据库列表
cur.execute("select * from persons")
for row in cur:
    print(row)
print ("select success")
conn.close()

5.查询数据

将 excel 数据导入 数据库表 mytable02 中

import pandas as pd
from sqlalchemy import create_engine
import sqlite3
file ="PittsburghBridges.xls"
df = pd.read_excel(file)
con=sqlite3.connect('mydata.db')
df.to_sql('mytable02',con=con,if_exists='replace',index=False)

# 增加主键
with engine.connect() as con:
    con.execute("""ALTER TABLE `{}`.`{}` \
            ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT FIRST, \
            ADD PRIMARY KEY (`id`);"""
                .format(db_name, table_name))

a:

SELECT name, mile FROM bridge where river = 'M' and demplished = '0';

b:

SELECT name, mile FROM bridge where type = 'arch' and material = 'steel'