简单数据库操作

1、python安装pymysql
pip install pymysql

2、安装、并设置数据库
关闭防火墙
setenforce 0
systemctl stop firewalld
systemctl enable firewalld

安装数据
yum install mariadb-server -y
systemtl start mariadb

设置数据库
mysql
MariaDB [(none)]> use mysql; 
MariaDB [mysql]> UPDATE user SET password=password('123456‘) WHERE user='root';  		#设置root密码
MariaDB [mysql]> flush privileges;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;		#授权
MariaDB [mysql]> flush privileges;

CREATE database my_mysql charset="utf8"			#创建数据库
use my_mysql
设置表创建表
CREATE  table goods( 
	id int unsigned primary key auto_increment not null, 
	name varcha(150) not null, 
	cname varchar(40) not null, 
	bname varchar(40) not null, 
	price decimal(10,3) not null default 0);

添加数据
MariaDB [my_mysql]>insert into goods (name, cname, bname, price) values ("www", "python", "java", 5000)
删除数据
MariaDB [my_mysql]> delete from goods where id=1;
修改数据
MariaDB [my_mysql]> update goods set bname="linux";
查询语句
MariaDB [my_mysql]> select * from goods;

import pymysql

# 创建数据连接
conn = pymysql.connect(
    host = "172.16.238.130",    # 主机地址
    port = 3306,                # 主机端口 
    db = "my_mysql",            # 数据库名
    user = "root",              # 登入数据库用户
    password = "123456",        # 登入数据库密码
    charset = "utf8")           # 通信采用的编码方式，推荐使用utf8

# 连接数据库创建游标
myCursor = conn.cursor()

# 编写sql语句
sql = '''insert into goods (name, cname, bname, price)
    values ("www", "python", "java", 5000)
    '''

# 把sql语句交给游标
myCursor.execute(sql)

# 通过连接，进行数据的提交
conn.commit()

# 关闭游标sql
myCursor.close()

# 关闭数据的连接
conn.close()