--------------------- pymysql | --------------------- * ²Ù×÷²½Öè 1,ͨ¹ý pymysql »ñÈ¡ connection Á¬½Ó¶ÔÏó 2,ͨ¹ý connection Á¬½Ó¶ÔÏó»ñÈ¡ cursor Óαê¶ÔÏó 3,ͨ¹ý cursor Ö´ÐÐ CRUD ²Ù×÷ 4,ͨ¹ý connection Ìá½»ÊÂÎñ --------------------- pymysql | --------------------- connect() * »ñÈ¡mysqlµÄÁ¬½Ó * ÃüÃû²ÎÊý host * Á¬½ÓµØÖ· port * Á¬½Ó¶Ë¿Ú user * Á¬½ÓÓû§Ãû password * Á¬½ÓÃÜÂë db * Êý¾Ý¿âÃû³Æ charset * Êý¾Ý¿âµÄ×Ö·û¼¯ cursorclass * ¸ÃÖµ¾ö¶¨ÁËÖ´ÐÐ SELECT ºó·µ»ØµÄ tuple ÖÐÊÇ tuple(ĬÈÏ) »¹ÊÇ dict(keyÊÇDB×Ö¶ÎÃû³Æ) * ¿ÉÑ¡Öµ pymysql.cursors.DictCursor * ½á¹û¼¯»á±»Ó³ÉäΪ dict --------------------- Connection | --------------------- cursor(cursor) * »ñÈ¡Ö´ÐÐSQLµÄÓαê * ²ÎÊý¾ö¶¨ÁËÖ´ÐÐ SELECT ºó·µ»ØµÄ tuple ÖÐÊÇ tuple(ĬÈÏ) »¹ÊÇ dict(keyÊÇDB×Ö¶ÎÃû³Æ),¸Ã²ÎÊý¾ß±¸Ä¬ÈÏÖµ * ¿ÉÑ¡²ÎÊý pymysql.cursors.DictCursor * ½á¹û¼¯»á±»Ó³ÉäΪ dict autocommit() * ÉèÖÃÊÂÎñÌá½»·½Ê½ÊÇ·ñ×Ô¶¯Ìá½»,ĬÈÏÊÖ¶¯Ìá½» * ²ÎÊýΪ bool Öµ begin() * ¿ªÊ¼ÊÂÎñ commit() * Ìá½»ÊÂÎñ rollback() * »Ø¹öÊÂÎñ close() * ¹Ø±ÕÁ¬½Ó --------------------- cursor | --------------------- execute(sql,param) * Ö´ÐÐSQLÓï¾ä * parma ÊÇÒ»¸öÐòÁÐ,Ìî³äsqlÖÐµÄ %s ռλ·û * ·µ»Ø½á¹ûÊÇÊܵ½Ó°ÏìµÄÐÐÊý executemany(sql,params) * Ö´ÐÐSQLÓï¾ä * params ÊÇÒ»¸öǶÌ×ÐòÁÐ,»áÑ­»·µÄÌî³äռλ·û %s ²¢ÇÒÖ´ÐÐ sql * ·µ»Ø½á¹ûÊÇÊܵ½Ó°ÏìµÄÐÐÊý * ·µ»Ø tuple tuple cursor.fetchall() * »ñÈ¡ËùÓеÄÊý¾Ý * Óαê»áÒƶ¯µ½×îºó * ·µ»Ø tuple tuple fetchmany(num) * »ñÈ¡Ö¸¶¨ÐÐÊýµÄÊý¾Ý,ÓαêºóÒÆnum¸öµ¥Î» * Èç¹ûnum³¬³öÊý¾ÝÐÐÊý,Ôò»á±»ÎÞÊÓ * ·µ»Ø tuple tuple fetchone() * »ñÈ¡µÚÒ»ÐÐÊý¾Ý,ÓαêÏòÇ°Òƶ¯Ò»¸öµ¥Î» * µ±ÓαêºóûÓÐÊý¾ÝµÄʱºò,·µ»Ø None None scroll(num,mode) * Òƶ¯Óαê,num,´ú±íÒƶ¯µÄµ¥Î» * ¹Ø¼ü×Ö²ÎÊý mode * ¸ÃÖµ¾ö¶¨ÁËÓαêµÄÒƶ¯Ä£Ê½ * relative µ±¶ÔÓÚµ±Ç°Î»ÖýøÐÐÒƶ¯ * absolute µ±¶ÔÓÚ¾ø¶ÔλÖýøÐÐÒƶ¯ * demo cursor.scroll(-1,mode='relative') * ÔÚµ±Ç°Î»ÖÃ,Ö¸ÕëÏòÇ°Òƶ¯Ò»Î» cursor.scroll(0,mode='absolute') * Ö¸ÕëÒƵ½¿ªÍ· close() * ¹Ø±ÕÓαê¶ÔÏó rowcount * ¸ÃÊôÐÔֵΪ,Êܵ½Ó°ÏìµÄÐÐÊý lastrowid * ¸ÃÊôÐÔֵΪִÐÐ INSERT ºó×îºóÒ»Ìõ¼Ç¼µÄ'×ÔÔö³¤idÖµ' --------------------- demo | --------------------- import pymysql import time db_host = '59.110.167.11' db_port = 1124 db_user = 'root' db_pass = 'KevinBlandy_mysql' db_database = 'demo' db_charset = 'utf8mb4' db_cursorclass = pymysql.cursors.DictCursor # ´´½¨Á¬½Ó¶ÔÏó conn = pymysql.connect(host=db_host,port=db_port,user=db_user,passwd=db_pass,db=db_database,charset=db_charset,cursorclass=db_cursorclass) # ͨ¹ýÁ¬½Ó¶ÔÏó,´´½¨Óαê cursor = conn.cursor() # ͨ¹ýÓαêÖ´ÐÐ INSERT ²Ù×÷,·µ»ØÊÜÓ°ÏìµÄÐÐÊý for i in range(100): affected_rows = cursor.execute('INSERT INTO `demo`(`id`,`name`,`age`,`phone`,`create_date`) VALUES(%s,%s,%s,%s,%s)',(i,'KevinBlandy',23,'18523570974',time.localtime())) # ͨ¹ýÓαêÖ´ÐÐ SELECT ²Ù×÷,·µ»Ø¼ìË÷µ½µÄÊý¾ÝÐÐÊý cursor.execute('SELECT * FROM `demo`') #ͨ¹ýÓαê»ñÈ¡Ö´ÐкóµÄ½á¹û for row in cursor.fetchall(): print(row) # ͨ¹ýÓαêÖ´ÐÐ UPDATE ²Ù×÷,·µ»ØÊÜÓ°ÏìµÄÐÐÊý cursor.execute('UPDATE `demo` SET `name` = %s WHERE `id` = %s',('ÓàÎÄÅó',0)) # ͨ¹ýÓαêÖ´ÐÐ DELETE ²Ù×÷,·µ»ØÊÜÓ°ÏìµÄÐÐÊý cursor.execute('DELETE FROM `demo` WHERE 1=1') # ͨ¹ýÁ¬½Ó¶ÔÏó,Ìá½»ÊÂÎñ conn.commit() # ÊÍ·ÅÁ¬½Ó conn.close()