python --- mysql启动与基本用法

python — mysql启动与基本用法
http://www.cnblogs.com/fnng/p/3565912.html
http://blog.csdn.net/lmss82/article/details/4414178

root@kali:~/python/socket/ftp# /etc/init.d/mysql start
[ ok ] Starting MySQL database server: mysqld . ..
[info] Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..

root@kali:~/python/socket/ftp# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
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> 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| csvt               |
| csvt04             |
| cvst               |
| mysql              |
| performance_schema |
+--------------------+
6 rows in set (0.04 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> use csvt04;
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> 


root@kali:~/python# python
Python 2.7.3 (default, Mar 14 2014, 11:57:14) 
[GCC 4.7.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>> conn = MySQLdb.connect(host='localhost',user='root',passwd='',db='csvt04',port=3306)
>>> cur = conn.cursor()
>>> cur.execute('show tables;')
12L
>>> cur.close()
>>> conn.close()
>>> 

#错误信息
>>> conn = MySQLdb.connect(host='localhost',user='root',passwd='173605852',db='csvt04',port='3306')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/dist-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 187, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
TypeError: an integer is required
>>> conn = MySQLdb.connect(host='192.168.72.130',user='root',passwd='173605852',db='csvt04',port='3306')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/dist-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 187, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
TypeError: an integer is required
>>> conn = MySQLdb.connect(host='192.168.72.130',user='root',passwd='173605852',db='csvt04',port=3306)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/dist-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 187, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on '192.168.72.130' (111)")
>>> cur.execute('showtables;')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "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 'showtables' at line 1")
>>> cur.execute('show tables;')
12L

2、取数据库的元素
数据库运行

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> 

代码情况

root@kali:~/python/mysql# vi mysql1.py
root@kali:~/python/mysql# cat mysql1.py
#!/usr/bin/python
# --*-- coding:utf-8 --*--

import MySQLdb
try:
    conn = MySQLdb.connect(host='localhost',user='root',passwd='',db='csvt04',port=3306)
    cur = conn.cursor()
    cur.execute('show tables;')
    #result = cur.fetchall()#fetchall()读取所有元素
    #result = cur.fetchmany(3)#只取前三条元素信息
    result = cur.fetchone()#只返回第一条结果
    for line in result:
        print line
    cur.close()
    conn.close()
except  MySQLdb.Error,e:
    print 'MySQL Error:',e

root@kali:~/python/mysql# 

运行情况

root@kali:~/python/mysql# vi mysql1.py

#取所有元素信息
root@kali:~/python/mysql# python mysql1.py
('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',)
root@kali:~/python/mysql# 
root@kali:~/python/mysql# vi mysql1.py

#取前三条信息
root@kali:~/python/mysql# python mysql1.py
('auth_group',)
('auth_group_permissions',)
('auth_message',)
root@kali:~/python/mysql# vi mysql1.py

#取第一条信息
root@kali:~/python/mysql# python mysql1.py
auth_group
root@kali:~/python/mysql#