VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > temp > 简明python教程 >
  • DataAnalysis-读取本地数据(2)

, -0.1214, -0.2481, -0.1075, -0.1445, -0.1173, -0.0204, -0.0318, -0.0157, 0.0927, 0.1974, 0.1549, 0.1598, 0.2948, 0.1754, -0.0013, -0.0455, -0.0471, -0.055, -0.1579, -0.0095, 0.0288, 0.0997, -0.1118, -0.1305, -0.1945, 0.0538, 0.1145, 0.064, 0.0252, 0.0818, 0.0924, 0.11, -0.1461, -0.0752, -0.0204, -0.0112, -0.0282, 0.0937, 0.0383, -0.0775, 0.028, 0.1654, -0.0698, 0.006, -0.0769, 0.1996, 0.1139, 0.2288, 0.2651, 0.3024, 0.1836, 0.3429, 0.151, 0.1357, 0.2308, 0.371, 0.377, 0.2982, 0.435, 0.4079, 0.2583, 0.2857, 0.342, 0.4593, 0.3225, 0.5185, 0.6335, 0.4427, 0.4255, 0.5455, 0.6018, 0.6145, 0.5806, 0.6583, 0.6139, 0.6113, 0.5415, 0.6354, 0.7008, 0.5759, 0.6219, 0.6687, 0.7402, 0.899]
复制代码
import pandas as pd

# 构建 dataframe
year_se = pd.Series(year_lst, name = 'year')
temp_se = pd.Series(temp_lst, name = 'temperature')
result_df = pd.concat([year_se, temp_se], axis = 1)
print (result_df.head())

# 保存csv
result_df.to_csv('./files/json_to_csv.csv', index = None)
复制代码
   year  temperature
0  1880      -0.1247
1  1881      -0.0707
2  1882      -0.0710
3  1883      -0.1481
4  1884      -0.2099

写json操作

复制代码
book_dict = [{'书名':'无声告白', '作者':'伍绮诗'}, {'书名':'我不是潘金莲', '作者':'刘震云'}, {'书名':'沉默的大多数 (王小波集)', '作者':'王小波'}]

filename = './files/json_output.json'
with open(filename, 'w') as f_obj:
    f_obj.write(json.dumps(book_dict, ensure_ascii=False))
# 不需要加, encoding='utf-8'参数

四、SQLite基本操作

连接数据库

复制代码
import sqlite3

db_path = './files/test.sqlite'

conn = sqlite3.connect(db_path)
cur = conn.cursor()
conn.text_factory = str  # 处理中文

获取基本信息

复制代码
cur.execute('SELECT SQLITE_VERSION()')

print ('SQLite版本:%s' %str(cur.fetchone()[0]))
复制代码
SQLite版本:3.30.0

逐条插入数据

复制代码
cur.execute("DROP TABLE IF EXISTS book")
cur.execute("CREATE TABLE book(id INT, name TEXT, price DOUBLE)")
cur.execute("INSERT INTO book VALUES(1,'肖秀荣考研书系列:肖秀荣(2017)考研政治命题人终极预测4套卷',14.40)")
cur.execute("INSERT INTO book VALUES(2,'法医秦明作品集:幸存者+清道夫+尸语者+无声的证词+第十一根手指(套装共5册) (两种封面随机发货)',100.00)")
cur.execute("INSERT INTO book VALUES(3,'活着本来单纯:丰子恺散文漫画精品集(收藏本)',30.90)")
cur.execute("INSERT INTO book VALUES(4,'自在独行:贾平凹的独行世界',26.80)")
cur.execute("INSERT INTO book VALUES(5,'当你的才华还撑不起你的梦想时',23.00)")
cur.execute("INSERT INTO book VALUES(6,'巨人的陨落(套装共3册)',84.90)")
cur.execute("INSERT INTO book VALUES(7,'孤独深处(收录雨果奖获奖作品《北京折叠》)',21.90)")
cur.execute("INSERT INTO book VALUES(8,'世界知名企业员工指定培训教材:所谓情商高,就是会说话',22.00)")
复制代码
<sqlite3.Cursor at 0x2d2d64e7c00>

批量插入数据

复制代码
books = (
    (9, '人间草木', 30.00),
    (10,'你的善良必须有点锋芒', 20.50),
    (11, '这么慢,那么美', 24.80),
    (12, '考拉小巫的英语学习日记:写给为梦想而奋斗的人(全新修订版)', 23.90)
)
cur.executemany("INSERT INTO book VALUES(?, ?, ?)", books)
复制代码
<sqlite3.Cursor at 0x2d2d64e7c00>
复制代码
conn.commit()

查找数据

复制代码
cur.execute('SELECT * FROM book')
rows = cur.fetchall()

# 通过索引号访问
for row in rows:
    print ('序号: %i, 书名: %s, 价格: %.2f' %(row[0], row[1], row[2]))
复制代码
序号: 1, 书名: 肖秀荣考研书系列:肖秀荣(2017)考研政治命题人终极预测4套卷, 价格: 14.40
序号: 2, 书名: 法医秦明作品集:幸存者+清道夫+尸语者+无声的证词+第十一根手指(套装共5册) (两种封面随机发货), 价格: 100.00
序号: 3, 书名: 活着本来单纯:丰子恺散文漫画精品集(收藏本), 价格: 30.90
序号: 4, 书名: 自在独行:贾平凹的独行世界, 价格: 26.80
序号: 5, 书名: 当你的才华还撑不起你的梦想时, 价格: 23.00
序号: 6, 书名: 巨人的陨落(套装共3册), 价格: 84.90
序号: 7, 书名: 孤独深处(收录雨果奖获奖作品《北京折叠》), 价格: 21.90
序号: 8, 书名: 世界知名企业员工指定培训教材:所谓情商高,就是会说话, 价格: 22.00
序号: 9, 书名: 人间草木, 价格: 30.00
序号: 10, 书名: 你的善良必须有点锋芒, 价格: 20.50
序号: 11, 书名: 这么慢,那么美, 价格: 24.80
序号: 12, 书名: 考拉小巫的英语学习日记:写给为梦想而奋斗的人(全新修订版), 价格: 23.90
复制代码
conn.row_factory = sqlite3.Row
cur = conn.cursor() 
cur.execute('SELECT * FROM book')
rows = cur.fetchall()

# 通过列名访问
for row in rows:
    print ('序号: %i, 书名: %s, 价格: %.2f' %(row['id'], row['name'], row['price']))
复制代码
序号: 1, 书名: 肖秀荣考研书系列:肖秀荣(2017)考研政治命题人终极预测4套卷, 价格: 14.40
序号: 2, 书名: 法医秦明作品集:幸存者+清道夫+尸语者+无声的证词+第十一根手指(套装共5册) (两种封面随机发货), 价格: 100.00
序号: 3, 书名: 活着本来单纯:丰子恺散文漫画精品集(收藏本), 价格: 30.90
序号: 4, 书名: 自在独行:贾平凹的独行世界, 价格: 26.80
序号: 5, 书名: 当你的才华还撑不起你的梦想时, 价格: 23.00
序号: 6, 书名: 巨人的陨落(套装共3册), 价格: 84.90
序号: 7, 书名: 孤独深处(收录雨果奖获奖作品《北京折叠》), 价格: 21.90
序号: 8, 书名: 世界知名企业员工指定培训教材:所谓情商高,就是会说话, 价格: 22.00
序号: 9, 书名: 人间草木, 价格: 30.00
序号: 10, 书名: 你的善良必须有点锋芒, 价格: 20.50
序号: 11, 书名: 这么慢,那么美, 价格: 24.80
序号: 12, 书名: 考拉小巫的英语学习日记:写给为梦想而奋斗的人(全新修订版), 价格: 23.90
复制代码
conn.close()

五、SQLite_json操作

复制代码
import sqlite3

db_path = './files/test_join.sqlite'

conn = sqlite3.connect(db_path)
cur = conn.cursor()
复制代码
# 建 depaetment 表,并插入数据
cur.execute("DROP TABLE IF EXISTS department")
cur.execute("CREATE TABLE department(\
                id INT PRIMARY KEY NOT NULL, \
                dept CHAR(50) NOT NULL, \
                emp_id INT NOT NULL)")
depts = (
        (1, 'IT Builing', 1),
        (2, 'Engineerin', 2),
        (3, 'Finance', 7)
)
cur.executemany("INSERT INTO department VALUES(?, ?, ?)", depts)
复制代码
<sqlite3.Cursor at 0x2d2d64f70a0>
复制代码
conn.commit()

CROSS JOIN 交叉连接

复制代码
cur.execute("SELECT emp_id, name, dept FROM company CROSS JOIN department;")
rows = cur.fetchall()
for row in rows:
    print (row)
复制代码
# 建 company 表,并插入数据
cur.execute("DROP TABLE IF EXISTS company")
cur.execute("CREATE TABLE company(\
                    id INT PRIMARY KEY NOT NULL, \
                    name CHAR(50) NOT NULL, \
                    age INT NOT NULL, \
                    address CHAR(50) NOT NULL,\
                    salary DOUBLE NOT NULL)")
companies = (
        (1, 'Paul', 32, 'California', 20000.0),
        (2, 'Allen', 25, 'Texas', 15000.0),
        (3, 'Teddy', 23, 'Norway', 20000.0),
        (4, 'Mark', 25, 'Rich-Mond', 65000.0),
        (5, 'David', 27, 'Texas', 85000.0),
        (6, 'Kim', 22, 'South-Hall', 45000.0),
        (7, 'James', 24, 'Houston', 10000.0)
)
cur.executemany("INSERT INTO company VALUES (?, ?, ?, ?, ?)", companies)
复制代码
<sqlite3.Cursor at 0x2d2d64f70a0>

INNER JOIN 内连接

复制代码
cur.execute("SELECT emp_id, name, dept FROM company INNER JOIN department \
            ON company.id = department.emp_id;")
rows = cur.fetchall()
for row in rows:
    print (row)
复制代码
(1, 'Paul', 'IT Builing')
(2, 'Allen', 'Engineerin')
(7, 'James', 'Finance')

OUTER JOIN 外连接

复制代码
# 左连接
cur.execute("SELECT emp_id, name, dept FROM company LEFT OUTER JOIN department \
            ON company.id = department.emp_id;")
rows = cur.fetchall()
for row in rows:
    print (row)
复制代码
(1, 'Paul', 'IT Builing')
(2, 'Allen', 'Engineerin')
(None, 'Teddy', None)
(None, 'Mark', None)
(None, 'David', None)
(None, 'Kim', None)
(7, 'James', 'Finance')
复制代码
# 右连接 (目前不支持)
cur.execute("SELECT emp_id, name, dept FROM company RIGHT OUTER JOIN department \
            ON company.id = department.emp_id;")
rows = cur.fetchall()
for row in rows:
    print (row)
复制代码
---------------------------------------------------------------------------

OperationalError                          Traceback (most recent call last)

<ipython-input-41-ce0fc573748b> in <module>
      1 # 右连接 (目前不支持)
      2 cur.execute("SELECT emp_id, name, dept FROM company RIGHT OUTER JOIN department \
----> 3             ON company.id = department.emp_id;")
      4 rows = cur.fetchall()
      5 for row in rows:


OperationalError: RIGHT and FULL OUTER JOINs are not currently supported
复制代码
# 右连接,交换两张表
cur.execute("SELECT emp_id, name, dept FROM department LEFT OUTER JOIN company \
            ON company.id = department.emp_id;")
rows = cur.fetchall()
for row in rows:
    print (row)
复制代码
(1, 'Paul', 'IT Builing')
(2, 'Allen', 'Engineerin')
(7, 'James', 'Finance')
复制代码
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
      ON COMPANY.ID = DEPARTMENT.EMP_ID;
复制代码
  File "<ipython-input-43-a0833b733075>", line 1
    sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
                        ^
SyntaxError: invalid syntax

六、Excel文件操作

pandas.read_excel(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None, squeeze=False, dtype=None, engine=None, converters=None, true_values=None, false_values=None, skiprows=None, nrows=None, na_values=None, keep_default_na=True, verbose=False, parse_dates=False, date_parser=None, thousands=None, comment=None, skipfooter=0, convert_float=True, mangle_dupe_cols=True, **kwds)

df_fujian = pd.read_excel("./datafiles/fujian.xlsx",sheet_name='日数据')


相关教程