CentOS常用数据库安装和python使用(MySQL, PostgreSQL)

2020-01-02

PostgreSQL

安装postgresql-server

yum install postgresql-server postgresql-contrib

检查安装所在路劲

[root@172-12-0-100 ~]# rpm -ql postgresql-server | grep setup

/usr/bin/postgresql-setup

初始化数据库

postgresql-setup initdb

开启服务并设置为自启动

systemctl start postgresql

systemctl enable postgresql

查看当前版本并修改用户密码

[root@172-12-0-100 share]# psql --version

psql (PostgreSQL) 9.2.24

[root@172-12-0-100 share]# su - postgres

-bash-4.2$ psql -U postgres

psql (9.2.24)

Type "help" for help.

postgres=# ALTER USER postgres WITH PASSword '123456';

postgres-#

postgres-# q

-bash-4.2$

开启远程访问

缺省时，postgresql只接受主机本身发起的连接

[root@172-12-0-100 data]# netstat -anlp | grep 5432

tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 20737/postgres

tcp6 0 0 ::1:5432 :::* LISTEN 20737/postgres

修改监听地址

vim /var/lib/pgsql/data/postgresql.conf

修改#listen_addresses = 'localhost' 为 listen_addresses='*'

配置文件的路径可通过如下方式查找

配置信任连接

vim /var/lib/pgsql/data/pg_hba.conf

重启服务

systemctl restart postgresql

检查接口监听情况

设置防火墙，允许用户连接

firewall-cmd --permanent --add-port=5432/tcp

firewall-cmd –reload

创建新用户和测试用数据库

[root@172-12-0-100 share]# su - postgres

-bash-4.2$ psql -U postgres

psql (9.2.24)

Type "help" for help.

postgres=#

创建用户：

postgres=# create user admin with password '12345678';

CREATE ROLE

创建测试用数据库并指定拥有者，将数据库所有权限赋予该拥有者

postgres=# create database testdb owner admin;

CREATE DATABASE

postgres=#

postgres=# grant all on database testdb to admin;

GRANT

查看数据库(l)

退出(q)

postgres=# q

-bash-4.2$ exit

Logout

Python连接postgresql时，需要先安装psycopg2

pip install psycopg2

写一个python脚本测试数据库的连接情况

## 导入psycopg2包

import psycopg2

## 连接到一个给定的数据库

conn = psycopg2.connect(database="testdb", user="admin",

 password="12345678", host="10.10.11.250", port="5432")

## 建立游标，用来执行数据库操作

cursor = conn.cursor()

## 执行SQL命令

cursor.execute("CREATE TABLE test_conn(id int, name text, addr text)")

cursor.execute("INSERT INTO test_conn values(1,'Moses','Egypt')")

cursor.execute("INSERT INTO test_conn values(2,'Joshua','Israel')")

## 提交SQL命令

conn.commit()

## 执行SQL SELECT命令

cursor.execute("select * from test_conn")

## 获取SELECT返回的元组

rows = cursor.fetchall()

for row in rows:

 print('id = ',row[0], 'name = ', row[1], 'addr = ', row[2], 'n')

## 关闭游标

cursor.close()

## 关闭数据库连接

conn.close()

安装并初始化mysql数据库

当使用yum install mysql时，会直接安装MariaDB。MySQL先后被Sun, Oracle收购， MySQL之父的Michael以他女儿Maria的名字开始了MySQL的另外一个衍生版本：MariaDB.

yum install mariadb-server

查看服务启动情况和端口监听情况

systemctl status mariadb

监听端口

也可以通过执行mysql后，通过show global variables like 'port'进行查询

修改监听端口

在配置文件/etc/my.cnf文件中，直接增加一行port=3309，重启服务，即可将端口由缺省的3306修改为3309

重新加载服务

systemctl restart mariadb

一些配置说明

[mysqld]

skip-networking=1 #不监听任何网络接口（即netstat看不到3306端口）

datadir=/var/lib/mysql #数据库存放目录

创建测试数据库

[root@172-12-0-100 ~]# mysql

MariaDB [(none)]> create database testdb;

--创建可远程登录的用户

create user 'admin'@'%' identified by '12345678';

---授权该用户对testdb的所有权限

grant select,insert,delete,update,create,drop on testdb.* to 'admin'@'%' identified by '12345678';

flush privileges;--立即启用修改

如果创建错了，可删除该用户

DROPUSER'username'@'host';

放开防火墙端口：

firewall-cmd --permanent  --add-port=3306/tcp

firewall-cmd --reload

pip install pymysql

脚本示例：

# 导入pymysql模块

import pymysql

# 连接database

conn = pymysql.connect(host="10.10.11.250", user="admin", password="12345678",database="testdb",charset="utf8")

# 得到一个可以执行SQL语句的光标对象

cursor = conn.cursor()

# 创建表格

sql = """

CREATE TABLE USER1 (

id INT auto_increment PRIMARY KEY ,

name CHAR(10) NOT NULL UNIQUE,

age TINYINT NOT NULL

)ENGINE=innodb DEFAULT CHARSET=utf8;

"""

# 执行SQL语句

cursor.execute(sql)

#插入数据

sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);"

username = "Moses"

age = 180

# 执行SQL语句

cursor.execute(sql, [username, age])

# 提交事务

conn.commit()

username = "Joshua"

age = 120

cursor.execute(sql, [username, age])

conn.commit()

#查询数据

sql = "SELECT * from USER1;"

cursor.execute(sql)

results = cursor.fetchall()

print(results)

# 关闭光标对象

cursor.close()

# 关闭数据库连接

conn.close()