python 查询数据库 参数

一、安装、调试数据库

1.安装数据库,
2.开启数据库服务，并设置开机自启
3.数据库安全初始化

[root@localhost ~]# mysql_secure_installation

Set root password? [Y/n] y

Remove anonymous users? [Y/n] y

Disallow root login remotely? [Y/n] n   ## 允许root用户远程登陆

Remove test database and access to it? [Y/n] y

Reload privilege tables now? [Y/n] y

4.创建数据库westosdb，创建表userinfo

[root@localhost ~]# mysql -uroot -p

MariaDB [(none)]> create database westosdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [westosdb]> create table userinfo( username varchar(50) not null, password varchar(50) not null);
Query OK, 0 rows affected (0.10 sec)

MariaDB [westosdb]> desc userinfo;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(50) | NO   |     | NULL    |       |
| password | varchar(50) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [westosdb]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> show tables;


MariaDB [mysql]> select Host,User,Password from user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
| 127.0.0.1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
| ::1       | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)

5.创建远程用户，并授权；

MariaDB [(none)]> create user root@'%' identified by 'westos';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on westosdb.* to root@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for root@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
| GRANT ALL PRIVILEGES ON `westosdb`.* TO 'root'@'%'                                                    |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

6.添加防火墙策略

[root@localhost ~]# firewall-cmd --permanent --add-service=mysql 
success
[root@localhost ~]# firewall-cmd --reload 
success

[root@localhost ~]# netstat -antlpp | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      3006/mysqld         
tcp        0      0 172.25.254.2:3306       172.25.254.10:49202     ESTABLISHED 3006/mysqld

7.远程登陆数据库

[root@foundation10 Desktop]# mysql -h 172.25.254.2 -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.44-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> Ctrl-C -- exit!
Aborted

二、python中编辑数据库

conn = MySQLdb.connect(host='localhost', \
            user='root',passwd='123456', \
            db='testpython', \
            port=3306,charset='utf8')

host : MySQL 数据库地址
user: 数据库登陆用户名
passwd: 数据库登陆密码
db: 登陆数据库后,需要操作的库名
port: 数据库监听端口,默认为 3306
charset: 数据库编码

MySQL连接操作

• commit() 如果数据库表进行了修改,提交保存当前的数据。
• rollback() 如果有权限,就取消当前的操作,否则报错
• cursor() 游标指针。

游标操作

# 创建一个数据库游标
In [3]: cur = conn.cursor()
cursor() 提供的方法来进行操作,方法主要是 :
1. 执行命令
2. 接收结果

cursor 执行命令的方法:

execute(query, args): 执行单条 sql 语句。 query 为 sql 语句本身, args 为参数值的列表。执行后返回值为受影响的行数。

executemany(query, args): 执行单条 sql 语句 , 但是重复执行参数列表里的参数 , 返回值为受影响的行数
conn.commit()  ## 提交事务

1.安装MySQL-python
• 一定要进行设置密码,确保数据库安全;
• 配置文件为/etc/my.cnf;
• 修改数据库默认的编码全部改为 UTF­8;
• 查看编码格式:show variables like ‘%char%’;
• 安装MySQL­python,封装了 MySQL C 驱动的 Python 驱动。

[root@foundation10 Desktop]# yum search python | grep -i mysql   ## -i 忽略大小写
MySQL-python.x86_64 : An interface to MySQL
[root@foundation10 Desktop]# yum install MySQL-python.x86_64 -y

python2中测试

[root@foundation10 Desktop]# python2
Python 2.7.5 (default, Oct 11 2015, 17:47:16) 
[GCC 4.8.3 20140911 (Red Hat 4.8.3-9)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb as mysql
>>> mysql.connect(host='172.25.254.2',user='root',passwd='westos',db='westosdb')
<_mysql.connection open to '172.25.254.2' at 1c58040>
>>>

python3中测试

[root@foundation10 Desktop]# python3
Python 3.6.4 (default, Jun  1 2018, 18:57:40) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import mysql 
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'mysql'
## 缺少pymysql模块

1）安装pymysql

[root@foundation10 bin]# pip -V    ## 查询pip版本（由于版本为2.7，则下载时需要用pip3）
pip 7.1.0 from /usr/lib/python2.7/site-packages (python 2.7)

[root@foundation10 Desktop]# cd /usr/local/python3/bin/
[root@foundation10 bin]# ./pip3 install pymysql
Collecting pymysql
  Downloading https://files.pythonhosted.org/packages/32/e8/222d9e1c7821f935d6dba8d4c60b9985124149b35a9f93a84f0b98afc219/PyMySQL-0.8.1-py2.py3-none-any.whl (81kB)
    100% |████████████████████████████████| 81kB 6.6kB/s 
Installing collected packages: pymysql
Successfully installed pymysql-0.8.1

2）测试

[root@foundation10 bin]# python3
Python 3.6.4 (default, Jun  1 2018, 18:57:40) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-4)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pymysql  ## 模块导入成功
>>> pymysql.connect(host='172.25.254.2',user='root',passwd='westos',db='westosdb')
<pymysql.connections.Connection object at 0x7f2cb504d7b8>
>>>

2.pycharm中编辑数据库

#表格中添加内容

import pymysql  ## 导入pymysql模块
## 1. 连接数据库:
conn = pymysql.connect(host='172.25.254.2',
                       user='root',passwd='westos',
                       db='westosdb',charset='utf8')

# 2. 创建游标, 为了给数据库输入sql指令
cur = conn.cursor()

# 3. 对于数据库操作
##表格中添加内容
for num in range(10):
    username = 'user' + str(num + 1)
    insert_sqli = "insert into userinfo values('%s','%s');" %(username,'00000')
    print("执行SQL:",insert_sqli)
    cur.execute(insert_sqli) ##上传数据

conn.commit()  ##提交事务（刷新）

# conn.autocommit(True)  # 是否自动提交操作；

cur.close()    ##关闭游标

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


/usr/local/python3/bin/python3.6 /root/PycharmProjects/day10/03_数据库.py
执行SQL: insert into userinfo values('user1','00000');
执行SQL: insert into userinfo values('user2','00000');
执行SQL: insert into userinfo values('user3','00000');
......
执行SQL: insert into userinfo values('user10','00000');

MariaDB [westosdb]> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| hello    | 123      |
| user1    | westos   |
| user1    | 00000    |
| user2    | 00000    |
| user3    | 00000    |
| user4    | 00000    |
| user5    | 00000    |
| user6    | 00000    |
| user7    | 00000    |
| user8    | 00000    |
| user9    | 00000    |
| user10   | 00000    |
+----------+----------+
12 rows in set (0.00 sec)

更新表格

import pymysql

conn = pymysql.connect(host='172.25.254.2',
                       user='root',passwd='westos',
                       db='westosdb',charset='utf8')

cur = conn.cursor()

try:
    update_sqli = 'update userinfo set password=%s where username=%s'
    update_li = [('6666', 'user'+str(num)) for num in range(5,11)]

    cur.executemany(update_sqli, update_li)
except Exception as e:
    print("更新失败:", e)
else:
    print("更新成功!")

conn.commit()
cur.close()
conn.close()

/usr/local/python3/bin/python3.6 /root/PycharmProjects/day10/03_数据库.py
更新成功!

MariaDB [westosdb]> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| hello    | 123      |
| user1    | westos   |
| user1    | 00000    |
| user2    | 00000    |
| user3    | 00000    |
| user4    | 00000    |
| user5    | 6666     |
| user6    | 6666     |
| user7    | 6666     |
| user8    | 6666     |
| user9    | 6666     |
| user10   | 6666     |
+----------+----------+
12 rows in set (0.00 sec)

查看表

1.查看表的数据条数

import pymysql

conn = pymysql.connect(host='172.25.254.2',
                       user='root',passwd='westos',
                       db='westosdb',charset='utf8')

cur = conn.cursor()

res = cur.execute('select * from userinfo;')
print("表的数据条数:", res)

conn.commit(）
cur.close()
conn.close()

查看表的内容

• fetchall(self): 接收全部的返回结果行 .
• fetchmany(size=None): 接收 size 条返回结果行 . 如果 size 的值大于返回的结果行的数量 , 则会返回 cursor.arraysize 条数据 .
• fetchone(): 返回一条结果行 .

import pymysql

conn = pymysql.connect(host='172.25.254.2',
                       user='root',passwd='westos',
                       db='westosdb',charset='utf8')

cur = conn.cursor()

res = cur.execute('select * from userinfo;')
print("表的数据条数:", res)

print(cur.fetchone())  ##查看表格的一行内容,显示为元组
print(cur.fetchone())
print(cur.fetchone())

print(cur.fetchmany(size=5))  ##查看5行内容，从指针位置开始之后的五行

print(cur.fetchall())   # 查看从指针位置开始之后的内容

conn.commit(）
cur.close()
conn.close()

/usr/local/python3/bin/python3.6 /root/PycharmProjects/day10/03_数据库.py
表的数据条数: 12
('hello', '123')
('user1', 'westos')
('user1', '00000')
(('user2', '00000'), ('user3', '00000'), ('user4', '00000'), ('user5', '6666'), ('user6', '6666'))
(('user7', '6666'), ('user8', '6666'), ('user9', '6666'), ('user10', '6666'))

移动指针

• scroll(value, mode='relative'): 移动指针到某一行 .
如果 mode='relative', 则表示从当前所在行移动 value 条 （正数：向下移动；负数：向上移动）;
如果 mode='absolute', 则表示从结果集的第一行移动 value 条（正数：向下移动；负数：向上移动）;

相对路径

import pymysql

conn = pymysql.connect(host='172.25.254.2',
                       user='root',passwd='westos',
                       db='westosdb',charset='utf8')

cur = conn.cursor()

res = cur.execute('select * from userinfo;')
print("表的数据条数:", res)

print(cur.fetchall())
cur.scroll(-1,'relative')  # 根据当前位置向左移动一个指针;
print(cur.fetchmany(2))


conn.commit()
cur.close()
conn.close()

/usr/local/python3/bin/python3.6 /root/PycharmProjects/day10/03_数据库.py
表的数据条数: 12
(('hello', '123'), ('user1', 'westos'), ('user1', '00000'), ('user2', '00000'), ('user3', '00000'), ('user4', '00000'), ('user5', '6666'), ('user6', '6666'), ('user7', '6666'), ('user8', '6666'), ('user9', '6666'), ('user10', '6666'))
(('user8', '6666'), ('user9', '6666'))

绝对路径

print(cur.fetchall())
cur.scroll(1, mode='absolute')   # 绝对路径, 从第一行向下移动1行;
print(cur.fetchone())

/usr/local/python3/bin/python3.6 /root/PycharmProjects/day10/03_数据库.py
表的数据条数: 12
(('hello', '123'), ('user1', 'westos'), ('user1', '00000'), ('user2', '00000'), ('user3', '00000'), ('user4', '00000'), ('user5', '6666'), ('user6', '6666'), ('user7', '6666'), ('user8', '6666'), ('user9', '6666'), ('user10', '6666'))
('user1', 'westos')