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

2023-07-21 16:15:04 214

1.MySQLConnector

1.1创建连接

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

1.2Cursor

importmysql.connector
con=mysql.connector.connect(
host="localhost",port="3306",
user="root",password="password",
database="demo"
)
cursor=con.cursor()
sql="SELECTempno,job,salFROMt_bonus;"
cursor.execute(sql)
print(type(cursor))
foriincursor:
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.3SQL注入攻击

username=1OR1=1password=1OR1=1
在使用字符串直接拼接时OR之前不管对错，与OR结合都为true
解决方法——预编译（也可以提高速度）

1.4事务管理和异常处理

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

importmysql.connector
try:
con=mysql.connector.connect(
host="localhost",port="3306",
user="root",password="password",
database="demo"
)
con.start_transaction()
cursor=con.cursor()
sql="INSERTINTOt_dept(deptno,dname,loc)VALUES(%s,%s,%s);"
cursor.execute(sql,(60,"SALES","HUBAI"))
con.commit()
exceptExceptionase:
if"con"indir():
con.rollback()
print(e)
finally:
if"con"indir():
con.close()

1.5删除数据

importmysql.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="DELETEFROMt_deptWHEREdeptno=%s"
cursor.execute(sql,(70,))
con.commit()
exceptExceptionase:
if"con"indir():
con.rollback()
print(e)
#donotneedtoclosecon

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

importmysql.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="INSERTINTOt_dept(deptno,dname,loc)VALUES(%s,%s,%s);"
date=[[70,"SALES","BEIJING"],[80,"ACTOR","SHANGHAI"]]
cursor.executemany(sql,date)
con.commit()
exceptExceptionase:
if"con"indir():
con.rollback()
print(e)
#donotneedtoclosecon

2.数据库连接池

数据库的连接是昂贵的，一个连接要经过TCP三次握手，四次挥手，而且一台计算机的最大线程数也是有限的
数据库连接池技术就是先创建好连接，再直接拿出来使用

importmysql.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="INSERTINTOt_dept(deptno,dname,loc)VALUES(%s,%s,%s);"
cursor.execute(sql,(70,"SALES","HUBAI"))
con.commit()
exceptExceptionase:
if"con"indir():
con.rollback()
print(e)
#donotneedtoclosecon

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

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

热门推荐

随机推荐