python ---mysql基本操作

python —mysql基本操作
连接数据库

root@kali:~/python# mysql -u rootWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 47Server version: 5.5.41-0+wheezy1 (Debian)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show database;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || csvt               || csvt04             || cvst               || mysql              || performance_schema |+--------------------+6 rows in set (0.00 sec)mysql> use csvt04Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+----------------------------+| Tables_in_csvt04           |+----------------------------+| auth_group                 || auth_group_permissions     || auth_message               || auth_permission            || auth_user                  || auth_user_groups           || auth_user_user_permissions || blog_blog                  || blog_entry                 || django_content_type        || django_session             || django_site                |+----------------------------+12 rows in set (0.00 sec)mysql> mysql> mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || csvt               || csvt04             || cvst               || mysql              || performance_schema || python             |+--------------------+7 rows in set (0.00 sec)mysql> use python;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+------------------+| Tables_in_python |+------------------+| host             |+------------------+1 row in set (0.00 sec)mysql> desc host    -> ;+--------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| host   | varchar(20) | YES  |     | NULL    |       || user   | varchar(20) | YES  |     | NULL    |       || passwd | varchar(20) | YES  |     | NULL    |       |+--------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> desc host;#查看目标表内元素+--------+-------------+------+-----+---------+-------+| Field  | Type        | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| host   | varchar(20) | YES  |     | NULL    |       || user   | varchar(20) | YES  |     | NULL    |       || passwd | varchar(20) | YES  |     | NULL    |       |+--------+-------------+------+-----+---------+-------+3 rows in set (0.01 sec)mysql> select * from host;Empty set (0.00 sec)mysql> select * from host;+----------------+-------+--------+| host           | user  | passwd |+----------------+-------+--------+| 192.168.72.130 | lilei | 123456 |+----------------+-------+--------+1 row in set (0.00 sec)mysql> mysql> mysql> select * from host;+----------------+-------+--------+| host           | user  | passwd |+----------------+-------+--------+| 192.168.72.130 | lilei | 123456 || 192.168.72.130 | lilei | 123456 || 192.168.72.130 | lilei | 123456 |+----------------+-------+--------+3 rows in set (0.00 sec)mysql> mysql> mysql> select * from host;+----------------+-------+--------+| host           | user  | passwd |+----------------+-------+--------+| 192.168.72.130 | lilei | 123456 || 192.168.72.130 | lilei | 123456 || 192.168.72.130 | lilei | 123456 |+----------------+-------+--------+3 rows in set (0.00 sec)#更改单个数据mysql> update host set host='192.168.72.132' where user='lilei' limit 1    -> ;Query OK, 1 row affected (0.04 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> update host set host='192.168.72.132' where user='lilei' limit 1;Query OK, 0 rows affected (0.00 sec)Rows matched: 1  Changed: 0  Warnings: 0mysql> update host set host='192.168.72.133' where user='lilei' limit 2;Query OK, 2 rows affected (0.00 sec)Rows matched: 2  Changed: 2  Warnings: 0mysql> select * from host;+----------------+-------+--------+| host           | user  | passwd |+----------------+-------+--------+| 192.168.72.133 | lilei | 123456 || 192.168.72.133 | lilei | 123456 || 192.168.72.130 | lilei | 123456 |+----------------+-------+--------+3 rows in set (0.00 sec)mysql> update host set host='192.168.72.131' where user='lilei' limit 1;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from host;+----------------+-------+--------+| host           | user  | passwd |+----------------+-------+--------+| 192.168.72.131 | lilei | 123456 || 192.168.72.133 | lilei | 123456 || 192.168.72.130 | lilei | 123456 |+----------------+-------+--------+3 rows in set (0.00 sec)mysql> 

代码：

root@kali:~/python/mysql# cat mysql2.py#/usr/bin/python# --*-- coding:utf-8 --*--import MySQLdbtry:    conn = MySQLdb.connect(host='localhost',user='root',passwd='',port=3306)#未指定数据库    cur = conn.cursor()#创建数据库游标    cur.execute('create database if not exists python')#如果数据库python不存在则新建    conn.select_db('python')#使用数据库python    #在数据库创建中host表    cur.execute('create table host(host varchar(20),user varchar(20),passwd varchar(20))')      #向test表里面插数据    value =['192.168.72.130','lilei','123456']    cur.execute('insert into host values(%s,%s,%s)',value)    conn.commit()#提交所有sql语句    cur.close()    conn.close()except MySQLdb.Error,e:    print 'MySQLdb Erroe %d:%s' % (e.args[0],e.args[1])root@kali:~/python/mysql# root@kali:~/python/mysql# python mysql2.pymysql2.py:8: Warning: Can't create database 'python'; database exists  cur.execute('create database if not exists python')#如果数据库python不存在则新建MySQLdb Erroe 1050:Table 'host' already existsroot@kali:~/python/mysql# vi mysql2.pyroot@kali:~/python/mysql# python mysql2.pyroot@kali:~/python/mysql# python mysql2.pyroot@kali:~/python/mysql#