python + MySql 的基础使用

MySql

登陆

mysql -u 账户名 -p

我的MySql版本是8.0.19

\[a-zA-Z!?#.]式的命令列表

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
输入help、\h、查看下面这个命令列表，输入\c清空当前输入的陈述。

?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
notee     (\t) Don't write into outfile.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.

使用方法：

1. 输入\h，弹出上面的help菜单
2. 输入\! cls，则会运行cmd本身的清空窗口命令
3. 等…

文本命令

List of all MySQL commands:
Note that all text commands must be first on line and end with ‘;’
注意所有 文本命令 都必须以新的一行开头，并且以分号结束。

文本命令	解释	效果
show databases;	查看所有数据库
create database 数据库名;	创建数据库	\
use 数据库名;	指定数据库	\
show tables;	查看当前库下的所有表
desc 表名;	查看某个表的所有字段， 如图有9个字段， 可以看到： 1、字段类型， 2、是否空值， 3、主键是哪个字段， 4、默认值都是NULL空值
select count(1) from 表名；	获取表的行数
select table_rows from information_schema.tables where table_schema=‘cov_010720’ and table_name=‘pv_days’;	使用 information_schema 方法来查看数据库中的表信息
1、alter table pv_days modify dt date not null first; 2、first可以改为after 字段，即把列调整到某列之后 3、modify可以改为change，但是change后面要加上当前字段的旧名字+空格+新名字	将某列改为第一列
select *,(cofm-heal-dead+nadd) as tj from pv_days where dt=‘2020-07-02’ order by tj desc;	查看今天的数据
select *,(cofm-heal-dead+nadd) as xc from pv_days where dt between ‘2020-06-03’ and ‘2020-07-03’ and province=‘北京’;	查询日期之间的数据
select province, sum(if(dt=‘2020-07-01’,cofm,null)) ‘2020-07-01’, sum(if(dt=‘2020-07-02’,cofm,null)) '2020-07-02’ from pv_days group by province;	将行字段转为列字段，类似Pivot

建表语句

CREATE TABLE 表名(
字段1 INT NOT NULL AUTO_INCREMENT,
字段2 VARCHAR(100) NOT NULL,
字段3 VARCHAR(40) NOT NULL,
字段4 DATE,
字段5 int(11) COMMENT '描述',
PRIMARY KEY ( runoob_id ) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

1、创建双主键表

create table Pv_days (
province varchar(3),
dt date,
cofm smallint not null,
heal smallint not null,
dead smallint not null,
nadd smallint not null,
primary key (province, dt)
) engine=innodb default charset=utf8;

示例：
# 示例一：
create table Pv_days (
    -> dt date,
    -> province varchar(3),
    -> cofm mediumint not null,
    -> heal mediumint not null,
    -> dead mediumint not null,
    -> nadd mediumint not null,
    -> primary key (dt, province)
    -> ) engine=innodb default charset=utf8;
    
# 示例二：
create table hot_search (
ts timestamp,
text varchar(64) not null,
primary key (ts)
) engine=innodb default charset=utf8;

修改字段类型

alter table sbage modify column name varchar(20);

修改字段键定义

1、将字段的键设置改为唯一性。

alter table sbage add unique(name);

2、将字段的空值设置改为非空。

alter table Pv_days
modify cofm smallint not null,
modify heal smallint not null,
modify dead smallint not null,
modify nadd smallint not null;

查询

1、出现warning时，查看warning。

2、更改表格的排序最终失败了…

2、限制查询数量为3条

3、求和查询

pymysql

pyqt建表：

CREATE TABLE 'details' (
'id' int(11) NOT NULL AUTO_INCREMENT,
'update_time' datetime DEFAULT NULL COMMENT '数据最后更新时间',
'record_time' datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
'province' varchar(50) DEFAULT NULL COMMENT '省',
'city' varchar(50) DEFAULT NULL COMMENT '累计确诊',
'confirm' int(11) DEFAULT NULL COMMENT '累计确诊',
'confirm_add' int(11) DEFAULT NULL COMMENT '新增确诊',
'heal' int(11) DEFAULT NULL COMMENT '累计治愈',
'dead' int(11) DEFAULT NULL COMMENT '累计死亡',
PRIMARY KEY ('id')
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

建立 - （conn）连接器对象 和 （游标/cursor）执行器对象，即可使用 游标 的 增删改 和 查 方法。

使用流程：

# 创建连接对象 参数db是：数据库名
conn = pymysql.connect(host='localhost', port=3306, user='root', password='331303', charset='utf8', db='netsources')
cursor = conn.cursor()

# 创建语句、数据等并执行相应方法
sql, datas = 增语句()
# sql, datas = 删语句()
# sql, datas = 改语句()
# sql = 查语句()
增删改的执行方法(sql, datas)
# 查的执行方法(sql)

# 关闭游标对象和连接器对象
cursor.close()
conn.close()

增删改-的执行方法

即会用到游标的commit()提交修改，出错则rollback()的方法：

def Excute_zsg(sql_lg, conn, cursor, values):
    try:
        cursor.executemany(sql_lg, values)
        # cursor.executemany('update ua_list set ua=%s where index', data) # 这句带有些参数，没搞明白
        conn.commit()
    except Exception as e:
        print(e)
        conn.rollback() # 当提交数据库失败时，使用游标回滚的方法回到上一次的样子。

查-的执行方法

即会用到游标的fetch()的方法：

def Excute_cha(sql_lg, conn, cursor, values):
	rows = cursor.excute(sql_lg) # excute系列的方法都会返回执行结果的行数
	resp = cursor.fetchall()

增语句

例1：将整个dataframe数据转换为python二维列表，插入到数据库。
创建insert的sql语句、并传递二维列表数据。

def Create_sql_lg(df，table_name, conn, cursor):
	# 1、创建sql语句
    keys = df.keys()
    key_sql = ','.join(keys) # 获取字段名
    values = df.values.tolist() # 返回列表, 如果有多列多行，那么就是二维列表！
    value_sql = ','.join(['%s']*df.shape[1]) # 字段数个 %s 用逗号相连的字符串
    insert_sql = 'insert into %s (%s) values (%s) on duplicate key update' % (table_name, key_sql, value_sql)
    update_str = ','.join([" {key} = values({key})".format(key=key) for key in keys])
    insert_sql += update_str
    return insert_sql, values

注意：

• pandas 的 DataFrame 默认会将数字类型字段的缺失值会用NAN填充，如果直接将数据用pymysql写入数据库会报错，此时需要将NAN替换为None , 这样插入到数据库才是正常的NULL值
• on duplicate key update 字段1 = values(字段1), ... ,字段n = values(字段n)，作用是：插入每行数据的过程中，当发现主键或唯一索引出现重复时，会使所选字段的新数据覆盖旧数据。

删语句

例1：清空数据库

def delete_all(cursor，table_name):
	cursor.execute(f'truncate {table_name}') # 清空数据库并让自增列归1

例1：删除符合某个条件的所有行

def delete_selections(cursor，table_name):
	cursor.execute('delete from table_name where 字段n = var')

改语句

def update_selections(cursor，table_name):
	cursor.execute('update sbage set table_name.字段1='yuyu' where table_name.字段2=15')

查语句

def Fetch(cursor，table_name):
	rows = cursor.execute(f'truncate {table_name}') # 清空数据库并让自增列归1
	res = cursor.fetchall() # 返回全部结果的列表，再拿就是空列表

除了fetchall()、还有fetchone() 获取1个，fetchmany(3) 获取 3个的方法。

运用

1、查看记录更新情况，再做判断。

sql = 'insert into details (update_time, province, city) values(%s, %s, %s)'
sql_c = 'select %s=(select update_time from details order by id desc limit 1)'
cursor.execute(sql_c, li[0][0])
if not cursor.fetchone()[0]:
	print(f'{time.asctime()}开始更新数据')
	for item in li:
		cursor.execute(sql, item)
	conn.commit(f'{time.asctime()}更新最新数据完毕')
	print()
else:
	print(f'{time.asctime()}已是最新数据')

Python爬取疫情实战

问题

MySQL账号密码忘记解决方法

# 第一次用临时密码进入后：
alter user user() identified by 'password';

# 以后再次修改密码：
alter user "root"@"localhost" identified by "password";

MySQL命令你知道少

mysql自增不连续的问题

ERROR 1170 (42000): BLOB/TEXT column ‘name’ used in key specification without a key length
解决办法：
1、是舍弃TEXT/BLOB列作为索引，或者设置另外的列作为主键。
2、如果不得不设置成主键，而想限制TEXT/BLOB的长度，可以尝试使用VARCHAR并设置其长度。VARCHAR默认长度是255个字符，并且其长度必须在其声明之后在其括号中设置，例如，VARCHAR(200)将其设置成200个字符长度。

MySQL修改数据表（ALTER TABLE语句）