python连接mysql代码_Python操作MySQL数据库的示例代码

1. MySQL Connector

1.1 创建连接

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)

1.2 Cursor

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:

(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'))

1.3 SQL注入攻击

username=1 OR 1=1 password=1 OR 1=1

在使用字符串直接拼接时OR之前不管对错，与OR结合都为true

解决方法——预编译（也可以提高速度）

1.4 事务管理和异常处理

sql连接和使用异常处理异常

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()

1.5 删除数据

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

executemany() 反复执行一条SQL语句

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

2. 数据库连接池

数据库的连接是昂贵的，一个连接要经过TCP三次握手，四次挥手，而且一台计算机的最大线程数也是有限的

数据库连接池技术就是先创建好连接，再直接拿出来使用

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

以上就是Python操作MySQL数据库的示例代码的详细内容，更多关于Python操作MySQL数据库的资料请关注python博客其它相关文章！