python —mysql基本操作
连接数据库
root@kali:~/python# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.
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 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| csvt |
| csvt04 |
| cvst |
| mysql |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)
mysql> use csvt04
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
mysql> 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 names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> 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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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 MySQLdb
try:
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.py
mysql2.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 exists
root@kali:~/python/mysql# vi mysql2.py
root@kali:~/python/mysql# python mysql2.py
root@kali:~/python/mysql# python mysql2.py
root@kali:~/python/mysql#