#!/usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "blzhu"
"""
python study
Date：2017
"""
import pymysql
# import MySQLdb #python2中的產物

try:
  # 獲取一個數據庫連接，注意如果是UTF-8類型的，需要制定數據庫
  conn = pymysql.connect(host='localhost', user='root', passwd='root', db='zbltest1', port=3306, charset='utf8')
  cur = conn.cursor() # 獲取一個游標
  for i in range(1, 10):
    zbl_id = str(i)
    zbl_name = 'zbl'+str(i)
    zbl_gender = 'man'
    # print("%s,%s,%s" % (zbl_id,zbl_name,zbl_gender))
    # sql = "insert student VALUES (id='%s',name='%s',gender='%s')" % (zbl_id,zbl_name,zbl_gender)
    sql = "insert student VALUES ('%s','%s','%s')" % (zbl_id, zbl_name, zbl_gender)
    # print(sql)
    cur.execute(sql)
  conn.commit()# 將數據寫入數據庫

    # try:
    # cur.execute(sql)
      # cur.commit()
    # except:
    #   cur.rollback()
    #cur.execute("""INSERT INTO 'student' ('id','name','gender') VALUES (%s,%s,%s ,(zbl_id,zbl_name,zbl_gender,))""")
    #cur.execute("""INSERT INTO 'student' ('id','name','gender') VALUES (zbl_id,zbl_name,zbl_gender)""")

    # cur.execute("INSERT student VALUES (zbl_id,zbl_name,zbl_gender)")

  # cur.execute("INSERT student VALUES ('4', 'zbl4', 'man')")# 正確
  #cur.execute("INSERT INTO 'student' ('id','name','gender') VALUES ('4', 'zbl4', 'man')")#錯誤
  #cur.execute("INSERT student ('id','name','gender') VALUES ('4', 'zbl4', 'man')")


  cur.execute('select * from student')
  # data=cur.fetchall()
  for d in cur:
    # 注意int類型需要使用str函數轉義
    print("ID: " + str(d[0]) + ' 名字： ' + d[1] + " 性別： " + d[2])
  print("row_number:", (cur.rownumber))
  # print('hello')

  cur.close() # 關閉游標
  conn.close() # 釋放數據庫資源
except Exception:
  print("發生異常")
          

            
#!/usr/bin/env python
# -*- coding:utf-8 -*-
# __author__ = "blzhu"
"""
python study
Date：2017
"""
import pymysql
try:
  # 獲取一個數據庫連接，注意如果是UTF-8類型的，需要制定數據庫
  conn = pymysql.connect(host='localhost', user='root', passwd='root', db='zbltest1', port=3306, charset='utf8')
  cur = conn.cursor() # 獲取一個游標
  for i in range(1, 10):
    zbl_id = str(i)
    zbl_name = 'zbl'+str(i)
    zbl_gender = 'man'
    # print("%s,%s,%s" % (zbl_id,zbl_name,zbl_gender))
    # sql = "insert student VALUES (id='%s',name='%s',gender='%s')" % (zbl_id,zbl_name,zbl_gender)
    sql = "insert student VALUES ('%s','%s','%s')" % (zbl_id, zbl_name, zbl_gender)
    # print(sql)
    cur.execute(sql)
  conn.commit()# 將數據寫入數據庫
  cur.execute('select * from student')
  # data=cur.fetchall()
  for d in cur:
    # 注意int類型需要使用str函數轉義
    print("ID: " + str(d[0]) + ' 名字： ' + d[1] + " 性別： " + d[2])
  print("row_number:", (cur.rownumber))
  # print('hello')

  cur.close() # 關閉游標
  conn.close() # 釋放數據庫資源
except Exception:
  print("發生異常")
          

            
#!/usr/bin/python3
import pymysql
import types

db=pymysql.connect("localhost","root","123456","python");

cursor=db.cursor()

#創建user表
cursor.execute("drop table if exists user")
sql="""CREATE TABLE IF NOT EXISTS `user` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(255) NOT NULL,
   `age` int(11) NOT NULL,
   PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"""

cursor.execute(sql)


#user插入數據
sql="""INSERT INTO `user` (`name`, `age`) VALUES
('test1', 1),
('test2', 2),
('test3', 3),
('test4', 4),
('test5', 5),
('test6', 6);"""

try:
  # 執行sql語句
  cursor.execute(sql)
  # 提交到數據庫執行
  db.commit()
except:
  # 如果發生錯誤則回滾
  db.rollback()
  
  
#更新
id=1
sql="update user set age=100 where id='%s'" % (id)
try:
  cursor.execute(sql)
  db.commit()
except:
  db.rollback()
  
#刪除
id=2
sql="delete from user where id='%s'" % (id)
try:
  cursor.execute(sql)
  db.commit()
except:
  db.rollback()
  
  
#查詢
cursor.execute("select * from user")

results=cursor.fetchall()

for row in results:
  name=row[0]
  age=row[1]
  #print(type(row[1])) #打印變量類型 
            
              

  print ("name=%s,age=%s" % \
       (age, name))