目录：
-------------------------------------
一：使用mysql时，cmd笔记
二：如何使用python把csv文件内容导入mysql数据库
三：python作业：任务26，数据库实践（进行中）
-------------------------------------
一.使用cmd弄mysql时的一些内容（部分）
Microsoft Windows [Version 10.0.14393]
(c) 2016 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>D:‘D:\‘ is not recognized as an internal or external command,
operable program or batch file.
C:\Users\Administrator>D:/
‘D:/‘ is not recognized as an internal or external command,
operable program or batch file.
C:\Users\Administrator>
C:\Users\Administrator>D:
D:\>cd D:\BtSoft\mysql\MySQL5.5\bin>
The syntax of the command is incorrect.
D:\>cd D:\BtSoft\mysql\MySQL5.5\bin
D:\BtSoft\mysql\MySQL5.5\bin>mysql -hlocalhost -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.5.62-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
mysql> use mysql;
Database changed
mysql> INSERT INTO user
-> (host, user,password,
-> select_priv,inser_priv,update_priv)
-> VALUES(‘localhost‘,‘test2‘,
-> PASSWORD(‘123456‘),‘Y‘,‘Y‘,‘Y‘);
ERROR 1054 (42S22): Unknown column ‘inser_priv‘ in ‘field list‘
mysql> INSERT INTO user
-> (host, user,password,
-> select_priv,insert_priv,update_priv)
-> VALUES(‘localhost‘,‘test2‘,
-> PASSWORD(‘123456‘),‘Y‘,‘Y‘,‘Y‘);
ERROR 1062 (23000): Duplicate entry ‘localhost-test2‘ for key ‘PRIMARY‘
mysql> INSERT INTO user
-> (host, user,password,
-> select_priv,insert_priv,update_priv)
-> VALUES(‘localhost‘,‘test3‘,
-> PASSWORD(‘123456‘),‘Y‘,‘Y‘,‘Y‘);
Query OK, 1 row affected, 3 warnings (0.00 sec)
mysql> SELECT host, user, password FROM user WHERE user = ‘test3‘;
+-----------+-------+-------------------------------------------+
| host | user | password |
+-----------+-------+-------------------------------------------+
| localhost | test3 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants
-> show grants;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘show grants‘ at line 2
mysql> show grants for root@‘localhost‘;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ WITH GRANT OPTION |
| GRANT PROXY ON ‘‘@‘‘ TO ‘root‘@‘localhost‘ WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for test2@‘localhost‘;
+--------------------------------------------------------------------------------------------------------------+
| Grants for test2@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘test2‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘ |
| GRANT ALL PRIVILEGES ON `test2`.* TO ‘test2‘@‘localhost‘ |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
二.如何使用python把csv文件导入MySQL库
注：这种方法可在同个文件夹下放多个csv文件，这里只用一个文件来弄
csv文件来源：https://www.citibikenyc.com/system-data/
1.向数据库daiguoxi_test创建数据表
# -*- coding: utf-8 -*-
import pymysql
# 1.链接数据库
db = pymysql.connect(
host=‘127.0.0.1‘,
port=3306,
user=‘test2‘,
passwd=‘123456‘,
db=‘daiguoxi_test‘,
charset=‘utf8‘)
# 建立链接游标
cursor = db.cursor()
print (‘>> 已连接数据表，处理中...‘)
# 2.添加数据库表头（创建的字段，不要使用空格）
sql = ‘‘‘CREATE TABLE IF NOT EXISTS daiguoxi_test (
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`tripduration` CHAR(10),
`starttime` CHAR(30),
`stoptime` CHAR(30),
`start_station_id` CHAR(10),
`start_station_name` CHAR(60),
`start_station_latitude` CHAR(20),
`start_station_longitude` CHAR(20),
`end_station_id` CHAR(10),
`end_station_name` CHAR(60),
`end_station_latitude` CHAR(20),
`end_station_longitude` CHAR(20),
`bikeid` CHAR(10),
`usertype` CHAR(15),
`birth_year` CHAR(10),
`gender` CHAR(2)
)‘‘‘
cursor.execute(sql)
# 3.提交并关闭链接
cursor.close()
db.close()
print (‘>> Done.‘)
运行结果:
>> 已连接数据表，处理中... >> Done. [Finished in 2.2s]
2. 先读取指定目录的所有CSV文件，然后逐个读取并逐条写入MySQL
# -*- coding: utf-8 -*-
import pymysql,time
import glob,os
import pandas as pd
# 1.准备，指定目录
time_start = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) # 记录当前时间
print(‘>> 当前时间：‘,time_start)
print(‘>> 开始处理……‘)
filelocation = r"C:/Users/Administrator/Desktop/作业和实验/代码文件/mysql/"
# 2.链接数据库
print(‘>> 连接MySQL...‘)
db = pymysql.connect(
host=‘127.0.0.1‘,
port=3306,
user=‘test2‘,
passwd=‘123456‘,
db=‘daiguoxi_test‘,
charset=‘utf8‘)
# 建立链接游标
cursor = db.cursor()
print (‘>> 已连接数据表。‘)
# 3.查看本地新文件名
filenames=[]
os.chdir(filelocation) #指定目录
for i in glob.glob("*.csv"): # 获取指定目标下所有的CSV文件名
filenames.append(i[:-4]) # 文件名不包含“.csv”
count = len(filenames)
print(‘>> 本地文件：‘,count,‘个‘) # 如下是以“Num.**”为序号打印出每个文件名
for i in range(0,count): # 把0-9的数字用0补齐2位，也可以用zfill函数或者format格式化实现
if i<9:
ii = i+1
ij = ‘0‘+str(ii)
else:
ij = i+1
print(‘ - Num.‘, end=‘‘)
print(ij, filenames[i])
# 4.把新文件的数据提交mysql
print(‘>> 读取中...‘)
# MySQL语句
insert_sql = ‘insert into daiguoxi_test (tripduration,starttime,stoptime,start_station_id,start_station_name,start_station_latitude,start_station_longitude,end_station_id,end_station_name,end_station_latitude,end_station_longitude,bikeid,usertype,birth_year,gender) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)‘
# 开始逐个文件处理
for file_name in filenames:
print(" + 正在处理：", file_name,‘（第‘,filenames.index(file_name)+1,‘个）‘)
time_now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) # 记录处理每个文件的时间
print(‘ - 当前时间：‘, time_now)
data_csv = pd.read_csv(open(filelocation + file_name+‘.csv‘)) # 使用Pandas读取数据文件
# print(data_csv.head(3)) # 查看前3条数据
# print(data_csv.info()) # 查看数据表信息
# print(len(data_csv.index)) # 查看数据量
# print(data_csv.loc[2].values) # 查看指定某一行的数据
ii = 0 # 用于统计每个文件的数据量
for i in range(0,data_csv.shape[0]): # 逐行读取
row = data_csv.loc[i].values # 获取第i行数据
# print(i,‘>>:‘,data_csv.loc[i].values) # 打印第i行数据
cursor.execute(insert_sql, (str(row[0]), str(row[1]), str(row[2]), str(row[3]), str(row[4]), str(row[5]), str(row[6]), str(row[7]),
str(row[8]), str(row[9]), str(row[10]), str(row[11]), str(row[12]), str(row[13]), str(row[14])))
ii = i + 1
print(‘ - 提交数量：‘,ii,‘条‘)
# 5.结束
db.commit() # 提交记录
db.close() # 关闭db
cursor.close() # 关闭游标
time_finish = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) # 记录当前时间
print(‘>> 当前时间：‘,time_finish)
print(‘\n‘,end=‘‘)
print(‘>> Done.‘) #完毕
运行结果：
>> 当前时间： 2020-05-26 11:51:53 >> 开始处理…… >> 连接MySQL... >> 已连接数据表。 >> 本地文件： 1 个 - Num.01 201501-citibike-tripdata >> 读取中... + 正在处理： 201501-citibike-tripdata （第 1 个） - 当前时间： 2020-05-26 11:51:53 - 提交数量： 285552 条 >> 当前时间： 2020-05-26 11:56:12 >> Done. [Finished in 259.7s]
3.在SQLyog上查看MySQL中的daiguoxi_test数据库中的数据