pandas数据读写

最新推荐文章于 2024-08-15 09:08:31 发布

shifting_sand

最新推荐文章于 2024-08-15 09:08:31 发布

阅读量163

点赞数

分类专栏：数据分析文章标签：数据分析

本文链接：https://blog.csdn.net/shunjianxaioshi/article/details/103876407

版权

数据分析专栏收录该内容

4 篇文章 3 订阅

订阅专栏

读写CSV Excel

import numpy as np
import pandas as pd
from pandas import Series,DataFrame

pd.read_csv('./pop_result.csv',sep='\t')

在这里插入图片描述

df = pd.read_table('./pop_result.csv')
df.to_excel('./pop_result.xls',index = False)
pd.read_excel('./pop_result.xls')

sqlite 最简单数据库，Android手机上用就是这个数据库

import sqlite3
# sqlite 存储数据是文件，轻量级的数据库
conn = sqlite3.Connection('./data.sqlite')
df = pd.read_sql('select * from Weather_2017',conn)
display(df.shape,df.head())
df.to_sql('Weather_2019',conn,index = False)

在这里插入图片描述

连接mysql

import pymysql

conn = pymysql.connect(host = 'localhost',port = 3306,database = 'dushu',user = 'softpo', password = 'root',charset = 'utf8')

df  = pd.read_sql('select * from books',conn)
display(df.shape,df.head())

在这里插入图片描述

# 精确查询
df.query("book_name == '主人的溃败'")

在这里插入图片描述

# mysql pandas + pymysql只能读取
# 不能写入
# pip install mysqlclient
# pip install sqlalchemy

import sqlalchemy

'''# mysqlclient (a maintained fork of MySQL-Python)
engine = create_engine('mysql+mysqldb://scott:tiger@localhost/foo')'''
engine = sqlalchemy.create_engine('mysql+mysqldb://softpo:root@localhost/dushu?charset=utf8')
engine
#Engine(mysql+mysqldb://softpo:***@localhost/dushu?charset=utf8)

# 模糊查询，like 像
df.query("book_name like  '%的%'")
df2 = df.query("book_name.str.contains('的')")

df2.to_sql('book211',engine,index=False)
df2.to_sql('book985',engine,index=False)

with engine.connect() as conn:
#     修改了主键，增删改查都可以
# unsigned int8 - 128 ~ 127 无符号 0 ~ 255
    conn.execute('alter table book985 change id id int unsigned not null auto_increment primary key;')

with engine.connect() as conn:
    df = conn.execute('select * from book985 limit 100')

for i in df:
    print(i)