Python——数据库编程

mariadb安装与使用

[root@localhost ~]# yum install mariadb-server -y
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysql_secure_installation 
Enter current password for root (enter for none): 
Set root password? [Y/n] Y
New password: 
Re-enter new password: 
[root@localhost ~]# mysql -uroot -predhat

数据库基本操作
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

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

MariaDB [(none)]> use westos;
Database changed
MariaDB [westos]> show tables;
Empty set (0.00 sec)

MariaDB [westos]> create table linux(
    -> username varchar(10),
    -> password varchar(10)
    -> );
Query OK, 0 rows affected (0.41 sec)

MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.00 sec)

MariaDB [westos]> desc linux;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | YES  |     | NULL    |       |
| password | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MariaDB [westos]> insert into linux value("user1", "123");
Query OK, 1 row affected (0.05 sec)

MariaDB [westos]> select * from linux;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

MariaDB [westos]> update linux set password="233" where username="user1";
Query OK, 1 row affected (0.36 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [westos]> delete from linux where username="user1";
Query OK, 1 row affected (0.37 sec)

MariaDB [westos]> select * from linux;
Empty set (0.00 sec)

MariaDB [westos]> drop table linux;
Query OK, 0 rows affected (0.06 sec)

MariaDB [westos]> drop database westos;
Query OK, 0 rows affected (0.00 sec)

mariadb中文编码问题

MariaDB [(none)]> show variables like 'c%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8_general_ci            |
| collation_database       | latin1_swedish_ci          |
| collation_server         | latin1_swedish_ci          |
| completion_type          | NO_CHAIN                   |
| concurrent_insert        | AUTO                       |
| connect_timeout          | 10                         |
+--------------------------+----------------------------+

vim /etc/my.cnf.d/client.cnf 
[client]
    default-character-set=utf8

vim /etc/my.cnf.d/server.cnf 
[mysqld]
    character-set-server=utf8

systemctl restart mariadb
mysql -uroot -predhat

MariaDB [(none)]> show variables like 'c%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | utf8_general_ci            |
| collation_database       | utf8_general_ci            |
| collation_server         | utf8_general_ci            |
| completion_type          | NO_CHAIN                   |
| concurrent_insert        | AUTO                       |
| connect_timeout          | 10                         |
+--------------------------+----------------------------+

MariaDB [westos]> select *from linux;select *from linux;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| 张龙      | 123      |
+----------+----------+

[root@localhost ~]# yum install MySQL-python -y
[root@localhost ~]# ipython
In [1]: import MySQLdb as mysql
In [2]: mysql.connect(user="root",passwd="redhat",host='127.0.0.1',charset='utf8')
Out[2]: <_mysql.connection open to '127.0.0.1' at 275eea0>
In [3]: conn = mysql.connect(user="root",passwd="redhat",host='127.0.0.1',charset='utf8')

In [4]: cur = conn.cursor() #传标
In [5]: conn.select_db('westos')
In [6]: cur.execute('insert into linux values("user11","123");')
Out[6]: 1L
In [7]: conn.commit()   #更新

In [8]: cur.close()
In [9]: conn.close()

多条语句执行

import MySQLdb as mysql
conn = mysql.connect(user="root",passwd="redhat",host='127.0.0.1',charset='utf8')
cur = conn.cursor()

li = [("user1", "1213"),("user2", "234"),("user3","456")]
sqli = 'insert into wetos values("%s","%s");'
cur.executemany(sqli.li)
In [31]: cur.execute('select * from westos.linux;')
Out[31]: 3L

In [32]: cur.fetchone()
Out[32]: (u'user1', u'123')

In [33]: cur.fetchmany(3)
Out[33]: ((u'\u5f20\u9f99', u'123'), (u'user11', u'123'))

In [34]: cur.fetchall()
Out[34]: ()