Python操作MySQL数据库的示例代码

import mysql.connector
 config={
   "host":"localhost","port":"3306",
   "user":"root","password":"password",
   "database":"demo"
 }
 con=mysql.connector.connect(**config)
 import mysql.connector
 config={
   "host":"localhost","port":"3306",
   "user":"root","password":"password",
   "database":"demo"
 }
 con=mysql.connector.connect(**config)

import mysql.connector
 con=mysql.connector.connect(
   host="localhost",port="3306",
   user="root",password="password",
   database="demo"
 )
 cursor=con.cursor()
 sql="SELECT empno,job,sal FROM t_bonus;"
 cursor.execute(sql)
 print(type(cursor))
 for i in cursor:
   print(i)
 con.close()
 
 Result:
   <class 'mysql.connector.cursor_cext.CMySQLCursor'>
   (7369, 'CLERK', Decimal('8000.00'))
   (7499, 'SALESMAN', Decimal('1600.00'))
   (7521, 'SALESMAN', Decimal('1250.00'))
   (7566, 'MANAGER', Decimal('2975.00'))
   (7654, 'SALESMAN', Decimal('1250.00'))
   (7698, 'MANAGER', Decimal('2850.00'))
   (7782, 'MANAGER', Decimal('2450.00'))
   (7788, 'ANALYST', Decimal('3000.00'))
   (7839, 'PRESIDENT', Decimal('5000.00'))
   (7844, 'SALESMAN', Decimal('1500.00'))
   (7900, 'CLERK', Decimal('950.00'))
   (7902, 'ANALYST', Decimal('3000.00'))
   (7934, 'CLERK', Decimal('1300.00'))

import mysql.connector
 try:
   con=mysql.connector.connect(
     host="localhost",port="3306",
     user="root",password="password",
     database="demo"
   )
   con.start_transaction()
   cursor=con.cursor()
   sql="INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s);"
   cursor.execute(sql,(60,"SALES","HUBAI"))
   con.commit()
 except Exception as e:
   if "con" in dir():
     con.rollback()
   print(e)
 finally:
   if "con" in dir():
     con.close()

import mysql.connector,mysql.connector.pooling
 config={
   "host": "localhost", "port": "3306",
   "user": "root", "password": "password",
   "database": "demo"
 }
 try:
   pool=mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=5)
   con=pool.get_connection()
   con.start_transaction()
   cursor = con.cursor()
   sql = "DELETE FROM t_dept WHERE deptno=%s"
   cursor.execute(sql, (70,))
   con.commit()
 except Exception as e:
   if "con" in dir():
     con.rollback()
   print(e)
 # do not need to close con

import mysql.connector,mysql.connector.pooling
 config={
   "host": "localhost", "port": "3306",
   "user": "root", "password": "password",
   "database": "demo"
 }
 try:
   pool=mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=5)
   con=pool.get_connection()
   con.start_transaction()
   cursor = con.cursor()
   sql = "INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s);"
   date=[[70,"SALES","BEIJING"],[80,"ACTOR","SHANGHAI"]]
   cursor.executemany(sql, date)
   con.commit()
 except Exception as e:
   if "con" in dir():
     con.rollback()
   print(e)
 # do not need to close con

import mysql.connector,mysql.connector.pooling
 config={
   "host": "localhost", "port": "3306",
   "user": "root", "password": "password",
   "database": "demo"
 }
 try:
   pool=mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=5)
   con=pool.get_connection()
   con.start_transaction()
   cursor = con.cursor()
   sql = "INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s);"
   cursor.execute(sql, (70, "SALES", "HUBAI"))
   con.commit()
 except Exception as e:
   if "con" in dir():
     con.rollback()
   print(e)
 # do not need to close con