python之pymysql(一)

python版本：python3.6

#encoding:utf-8
'''author:iber/date:2017.10.11'''

import pymysql
try:
    conn = pymysql.connect(
        host='localhost',
        port= 3306,
        user='root',
        passwd='root',
        db='python_test',
        charset='utf8')   

    cur=conn.cursor()
    cur.execute('select * from user_info')
    data=cur.fetchall()
    for d in data :
        #注意int类型需要使用str函数转义
        print("Name: %-5s->ID: %-2s->Money: %-5s " %(str(d[0]),str(d[1]),str(d[2])))   #左对齐格式化控制
       #print(len(d)),
    cur.close()#关闭游标
    conn.close()#释放数据库资源
except  Exception as e:print("查询失败:"+str(e))

'''

实例：
#查询数据
sql = "select Name,id from user_info where money = '%s' "
data = ('a',)
cursor.execute(sql % data)

for row in cursor.fetchall():
        print("Name:%s id:%d" % row)
print('查询成功',cursor.rowcount,'条数据')

sql = "insert into user_info(Name,id,money) values('%s',%d,'%s')"
data = ('xtt',10,'c')
cursor.execute(sql % data)
conn.commit()
print('成功修改',cursor.rowcount,'条数据')

 
# 删除数据  
sql = "DELETE FROM money  WHERE account = '%s' LIMIT %d"  
data = ('13512345678', 1)  
cursor.execute(sql % data)  
conn.commit()  
print('成功删除', cursor.rowcount, '条数据')  
 
# 事务处理  
sql_1 = "UPDATE money SET saving = saving + 1000 WHERE account = '18012345678' "  
sql_2 = "UPDATE money SET expend = expend + 1000 WHERE account = '18012345678' "  
sql_3 = "UPDATE money SET income = income + 2000 WHERE account = '18012345678' "  
 
try:  
    cursor.execute(sql_1)  # 储蓄增加1000  
    cursor.execute(sql_2)  # 支出增加1000  
    cursor.execute(sql_3)  # 收入增加2000  
except Exception as e:  
    conn.rollback()  # 事务回滚  
    print('事务处理失败', e)  
else:  
    conn.commit()  # 事务提交  
    print('事务处理成功', cursor.rowcount)  
 
# 关闭连接  
cursor.close()  
conn.close()
'''

例子数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| python_test        |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use python_test;

mysql> show tables;
+-----------------------+
| Tables_in_python_test |
+-----------------------+
| user_info             |
+-----------------------+

mysql> desc user_infoG
*************************** 1. row ***************************
  Field: Name
   Type: varchar(20)
   Null: NO
    Key:
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: id
   Type: int(11)
   Null: NO
    Key: PRI
Default: NULL
  Extra: auto_increment
*************************** 3. row ***************************
  Field: money
   Type: varchar(10)
   Null: YES
    Key:
Default: NULL
  Extra:

mysql> show create table user_infoG
*************************** 1. row ***************************
       Table: user_info
Create Table: CREATE TABLE `user_info` (
  `Name` varchar(20) CHARACTER SET latin1 NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `money` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8