数据存储
mongo
1 """ 2 使用pymongo库操作MongoDB数据库 3 """ 4 import pymongo 5 6 # 1.连接数据库服务器,获取客户端对象 7 mongo_client=pymongo.MongoClient('localhost',27017) 8 9 # 2.获取数据库对象 10 db=mongo_client.myDB 11 # db=mongo_client['myDB'] 12 13 # 3.获取集合对象 14 my_collection=db.myCollection 15 # my_collection=db['myCollection'] 16 17 18 print("——"*50) 19 # 插入文档 20 tom={'name':'Tom','age':18,'sex':'男','hobbies':['吃饭','睡觉','打豆豆']} 21 alice={'name':'Alice','age':19,'sex':'女','hobbies':['读书','跑步','弹吉他']} 22 tom_id=my_collection.insert(tom) 23 alice_id=my_collection.insert(alice) 24 print(tom_id) 25 print(alice_id) 26 27 28 print("——"*50) 29 # 查询文档 30 cursor=my_collection.find() 31 print(cursor.count()) # 获取文档个数 32 for item in cursor: 33 print(item) 34 35 36 print("——"*50) 37 # 修改文档 38 my_collection.update({'name':'Tom'},{'$set':{'hobbies':['向Alice学习读书','跟Alice一起跑步','向Alice学习弹吉他']}}) 39 for item in my_collection.find(): 40 print(item) 41 42 43 print("——"*50) 44 # 删除文档 45 # my_collection.remove({'name':'Tom'},{'justOne':0}) 46 my_collection.remove() 47 for item in my_collection.find(): 48 print(item) 49 ''' 50 from pymongo import MongoClient 51 #连接MongoDB 52 conn=MongoClient(host='localhost',port=27017) 53 #client=pymongo.MongoClient('mogondb://localhost:27017/') 54 #指定数据库 55 db=conn['优酷python教程'] 56 print(db) 57 #db=client['优酷python教程'] 58 #指定集合 59 user=db['students'] 60 print(user) 61 #collection=db['students'] 62 #插入数据 63 student={ 64 'id':'20180101', 65 'name':'ray', 66 'age':20, 67 'gender':'male' 68 } 69 result=db.user.insert_one(student) 70 print(result 71 '''
excel
1 #操作2003excel 2 import xlwt,xlrd 3 #操作2007excel 4 import openpyxl 5 6 def write_excel_03(path,file_name,values): 7 wb = xlwt.Workbook() #新建一个excel 8 sheet = wb.add_sheet(file_name)#创建一个sheet页 9 row = 0#控制行 10 for value in values: 11 col = 0#控制列 12 for cell in value: 13 sheet.write(row,col,cell)#写数据 14 col+=1 15 row+=1 16 wb.save(path)#保存 17 print('03_excel write sucessful') 18 19 def read_excel_03(path,file_name): 20 print('excel_03 read start') 21 wb = xlrd.open_workbook(path)#打开一个excel 22 # sheets=wb.sheet_by_index(0)#根据顺序获取sheet 23 ws = wb.sheet_by_name(file_name)#根据sheet名字获取sheet 24 # print(ws.cell(0,0).value)#指定行列获取数据 25 # print(ws.ncols)#获取sheet中有多少列 26 # print(ws.nrows)#获取sheet中有多少行 27 # for row in ws.get_rows(): 28 # print(row) 29 # print(ws.row_values(0))#获取第一行 30 # print(ws.col_values(0)) # 获取第一列 31 print('data in rows') 32 for i in range(ws.nrows): 33 print(ws.row_values(i))#获取每一行的数据 34 print('data in cols') 35 for j in range(ws.ncols): 36 print(ws.col_values(j))#获取每一列的数据 37 print('------') 38 for i in range(ws.nrows): 39 for j in range(ws.ncols): 40 print(ws.cell_value(i,j),'\t',end='') 41 print() 42 43 def write_excel_07(path,file_name,value): 44 wb = openpyxl.Workbook() #创建excel 45 sheet =wb.active 46 sheet.title=file_name 47 for i in range(4): 48 for j in range(len(value[i])): 49 sheet.cell(row=i+1,column=j+1,value=str(value[i][j])) 50 wb.save(path) 51 print('07_excel write sucessful') 52 53 def read_excel_07(path,file_name): 54 wb = openpyxl.load_workbook(path) 55 #sheets = wb.get_sheet_by_name(file_name) #过时的写法 56 sheets = wb.worksheets[0] 57 for row in sheets.rows: 58 for cell in row: 59 print(cell.value,'\t',end='') 60 print() 61 62 if __name__ == '__main__': 63 values = [["名称", "价格", "出版社", "语言"], 64 ["python基础", "29.9", "机械工业出版社", "中文"], 65 ["数据分析", "66.99", "人民邮电出版社", "中文"], 66 ["机器学习", "38.88", "机械工业出版社", "中文"]] 67 path_2003 ='/home/chen/projects_src/excels/2003.xls' 68 path_2007 ='/home/chen/projects_src/excels/2007.xlsx' 69 sheet_2003 = '2003测试表' 70 sheet_2007 = '2007测试表' 71 72 write_excel_03(path_2003,sheet_2003,values) 73 read_excel_03(path_2003,sheet_2003) 74 # write_excel_07(path_2007,sheet_2007,values) 75 # read_excel_07(path_2007,sheet_2007)
csv
1 import csv 2 data=[ 3 ['id','name','age'], 4 ['10001','ray','27'], 5 ['10002','lucy','30'], 6 ['10003','alex','33'] 7 ] 8 with open('/home/chen/PycharmProjects/download/csv/优酷python教程.csv','w',encoding='utf-8') as csvfile: 9 #writer = csv.writer(csvfile)#writer初始化写入对象，默认,分割 10 writer = csv.writer(csvfile,delimiter=' ') # 空格分割 11 #writer.writerows(data)#一次写入多行 12 for line in data: 13 writer.writerow(line)#writerow 写入一行 14 #字典写入 15 fieldnames=['id','name','age']#头信息 16 writer=csv.DictWriter(csvfile,fieldnames)#初始化字典 17 writer.writeheader()#写入头信息 18 writer.writerow({'id':'10001','name':'ray','age':'27'})#写入字典 19 with open('/home/chen/PycharmProjects/download/csv/优酷python教程.csv','r') as csvfile: 20 reader=csv.reader(csvfile)#reader对象 21 for line in reader: 22 print(line)
mysql
1 import pymysql 2 def insert(table,keys,values,cursor,data,db): 3 sql = 'insert into {table}({keys}) values({values})'.format(table=table, keys=keys, values=values) 4 try: 5 if cursor.execute(sql,tuple(data.values())): 6 print('successful') 7 db.commit() 8 except: 9 print('failed') 10 db.rollback() 11 def update(table,keys,values,cursor,data,db): 12 sql='insert into {table}({keys}) values({values}) on duplicate key update'.format(table=table,keys=keys,values=values) 13 update=','.join([" {key}=%s".format(key=key) for key in data]) 14 sql+=update 15 print(sql) 16 try: 17 if cursor.execute(sql,tuple(data.values())*2): 18 print('update successful') 19 db.commit() 20 except: 21 print('update failed') 22 db.rollback() 23 def delete(table,condition): 24 sql='delete from {table} where {condition}'.format(table=table,condition=condition) 25 try: 26 cursor.execute(sql) 27 db.commit() 28 except: 29 db.rollback() 30 def query(table,condition,cursor): 31 sql='select * from {table} where {condition}'.format(table=table,condition=condition) 32 try: 33 cursor.execute(sql) 34 print('Count:',cursor.rowcount) 35 row=cursor.fetchone() 36 while row: 37 print('row:',row) 38 row=cursor.fetchone() 39 # results=cursor.fetchall() 40 # for row in results: 41 # print(row) 42 except: 43 print('Error') 44 def connect(): 45 db=pymysql.connect( 46 host='localhost', 47 user='root', 48 password='root', 49 port=3306, 50 db='spiders' 51 ) 52 return db 53 if __name__ == '__main__': 54 db=connect() 55 cursor=db.cursor() 56 data={ 57 'id':'10003', 58 'name':'lucy', 59 'age':'22' 60 } 61 #动态插入数据 62 table='students' 63 keys=','.join(data.keys()) 64 values=','.join(['%s']*len(data)) 65 #insert(table, keys, values, cursor, data, db) 66 #动态修改数据 67 data={ 68 'id':'10003', 69 'name':'lucy', 70 'age':'23' 71 } 72 update(table,keys,values,cursor,data,db) 73 #删除数据 74 # condition='age<25' 75 # delete(table,condition) 76 #查询 77 condition='age>20' 78 query(table,condition,cursor) 79 db.close() 80 ''' 81 #创建表 82 db=pymysql.connect( 83 host='localhost', 84 user='root', 85 password='root', 86 port=3306, 87 db='spiders' 88 ) 89 id='201800001' 90 name='ray' 91 age='27' 92 cursor=db.cursor() 93 sql='create table if not exists students(id varchar(255) not null,name varchar(255) not null,age int not null,primary key(id))' 94 cursor.execute(sql) 95 sql='insert into students(id,name,age) values(%s,%s,%s)' 96 try: 97 cursor.execute(sql,(id,name,age)) 98 db.commit() 99 except: 100 db.rollback() 101 sql='select * from students' 102 cursor.execute(sql) 103 db.close() 104 ''' 105 #创建数据库 106 # db=pymysql.connect( 107 # host='localhost', 108 # user='root', 109 # password='root', 110 # port=3306 111 # ) 112 # cursor=db.cursor() 113 # cursor.execute('select version()') 114 # data=cursor.fetchone() 115 # print('database version',data) 116 # cursor.execute('create database spiders default character set utf8')#创建数据库spiders 117 # db.close() 118 119 #E-R E:entry R:relationship 120 #crud create retrieve updata delete 121 # 三范式 列不可拆分 唯一标识 引用主键 122 123 #字段类型 124 #数组 int decimal 125 #字符串 char varchar text 126 #日期 datetime 127 #布尔 bit 128 129 #约束 130 #主键primary key 131 #非空not null 132 #唯一unique 133 #默认default 134 #外键foreign key 135 136 #命令行 137 #mysql --help 138 #mysql -uroot -p 连接数据库 139 #exit 退出 140 141 #创建数据库 create database db_name charset=utf8; 142 #删除数据库 drop database db_name 143 #显示所有数据库 show databases; 144 #查看当前选择的数据库 select database() 145 #切换数据库 use db_name 146 147 148 #创建表 149 ''' 150 create table job51( 151 id int auto_increment primary key not null, 152 positionName varchar(100) not null, 153 positionLink varchar(100) not null, 154 companyName varchar(100) not null, 155 companyLink varchar(100) not null, 156 wordLocation varchar(100) not null, 157 salary varchar(20) not null, 158 publishTime date) 159 create table t_name( 160 id int auto_increment primary key not null, 161 name varchar(10) not null, 162 gender bit default 1, 163 birthday datatime); 164 ) 165 ''' 166 #显示数据库下所有表 show tables 167 #查看表结构 desc t_name 168 #删除表 drop table 169 #修改表 170 ''' 171 alter table t_name add/change/drop cols type 172 eg:alter table t_name add isdelete bit default 0 173 ''' 174 #更改表名称 rename table old_cols to new_colds 175 #查看表的创建语句 show create table 't_name' 176 #插入 177 ''' 178 insert into t_name values(0,'ray',1,'1991-11-19',0); 179 insert into t_name(name) value('alex') 180 ''' 181 #查询 182 ''' 183 select * from t_name 184 185 #print(cursor.fetchone())#查找单行 186 #print(cursor.fetchmany(3))#查几行 187 print(cursor.fetchall(),'\n')#查找所有 188 '''
sqlalchemy
1 # Author:Ray 2 # 导入: 3 4 from sqlalchemy import Column, String, create_engine 5 from sqlalchemy.orm import sessionmaker 6 from sqlalchemy.ext.declarative import declarative_base 7 8 # 创建对象的基类: 9 Base = declarative_base() 10 11 # 定义User对象: 12 class User(Base): 13 # 表的名字: 14 __tablename__ = 'user' 15 16 # 表的结构: 17 id = Column(String(20), primary_key=True) 18 name = Column(String(20)) 19 20 # 初始化数据库连接: 21 engine = create_engine('mysql+mysqlconnector://root:root@localhost:3306/优酷python教程') 22 # 创建DBSession类型: 23 DBSession = sessionmaker(bind=engine) 24 25 # 创建session对象: 26 session = DBSession() 27 # 创建新User对象: 28 new_user = User(id='5', name='Bob') 29 # 添加到session: 30 session.add(new_user) 31 # 提交即保存到数据库: 32 session.commit() 33 # 关闭session: 34 session.close() 35 # 创建Session: 36 session = DBSession() 37 # 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行: 38 user = session.query(User).filter(User.id=='5').one() 39 # 打印类型和对象的name属性: 40 print('type:', type(user)) 41 print('name:', user.name) 42 # 关闭Session: 43 session.close() 44 # DROP TABLE students; 45 # CREATE TABLE students( 46 # stu_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 47 # stu_name VARCHAR(20) NOT NULL, 48 # age INT NOT NULL, 49 # gender CHAR(5) NOT NULL); 50 # INSERT INTO students(stu_name,age,gender) VALUES('ray',27,'m'); 51 # INSERT INTO students(stu_name,age,gender) VALUES('alex',28,'m'); 52 # INSERT INTO students(stu_name,age,gender) VALUES('lucy',35,'f'); 53 # INSERT INTO students(stu_name,age,gender) VALUES('chris',30,'m'); 54 # INSERT INTO students(stu_name,age,gender) VALUES('jack',29,'m'); 55 # INSERT INTO students(stu_name,age,gender) VALUES('suker',33,'m'); 56 # INSERT INTO students(stu_name,age,gender) VALUES('lili',25,'f'); 57 # SELECT * FROM students;
