python连接mysql
时间：2021-07-01 10:21:17
帮助过：27人阅读
!/usr/bin/python
# -*- coding:utf-8 -*-
import os, sys
import time
import re
import MySQLdb
reload(sys)
sys.setdefaultencoding("utf8")
#源系统数据库连接
SOURCE_URL =
"jdbc:mysql://xx.xx.xx.42:3306/risk?useUnicode=true&characterEncoding=utf-8"
print SOURCE_URL
# 源系统用户名
SOURCE_USER =
"admin"
print SOURCE_USER
# 源系统密码
SOURCE_PASSWD =
"admin@666"
print SOURCE_PASSWD
# 源系统数据库名称
SOURCE_DBNAME =
"risk"
print SOURCE_DBNAME
#获取MYSQL连接
def openConn(hostStr, port, userStr, passwdStr, tableSchema):
conn = MySQLdb.connect(host=
‘%s‘%(hostStr), user=
‘%s‘%(userStr), passwd=
‘%s‘%
(passwdStr),
db=
‘%s‘%(tableSchema), port=port, charset=
"utf8", use_unicode=
"True")
return conn
#获取查询结果
def getRs(conn, sql):
try:
cursor =
conn.cursor();
cursor.execute(sql);
rows =
cursor.fetchall();
conn.commit();
return rows;
except Exception as e:
print (str(e))
pass;
finally:
try:
cursor.close();
except:
pass;
#执行SQL
def execSql( conn, sql):
try:
cursor =
conn.cursor();
cnt =
cursor.execute(sql);
conn.commit();
return cnt
except Exception as e:
print (str(e))
pass;
finally:
try:
cursor.close();
except:
pass;
return -1
# 连接数据库：
def excuteSql_select(sql):
# 获取源系统mysql连接
if "mysql" in SOURCE_URL:
mysqlStr = SOURCE_URL.split(
":")
conn = openConn(mysqlStr[2].replace(
"//",
""), int(mysqlStr[3].split(
"/")[0]), SOURCE_USER, SOURCE_PASSWD,SOURCE_DBNAME)
return getRs(conn, sql)
else:
print "未知类型数据库"
sys.exit(1
)
# 连接数据库：
def excuteSql(sql):
# 获取源系统mysql连接
if "mysql" in SOURCE_URL:
mysqlStr = SOURCE_URL.split(
":")
conn = openConn(mysqlStr[2].replace(
"//",
""), int(mysqlStr[3].split(
"/")[0]), SOURCE_USER, SOURCE_PASSWD,SOURCE_DBNAME)
return execSql(conn, sql)
else:
print "未知类型数据库"
sys.exit(1
)
# 查询数据：
result_select = excuteSql_select(
"select count(*) from LOANCARDINFO")
print ‘查询结果总条数：‘ +
str(result_select[0][0])
# 执行操作：
result = excuteSql(
"insert into test values(3)")
print ‘插入数据成功：‘ + str(result)
python连接mysql
标签：系统数据库 查询 连接数 turn ace insert ecs tst sel
