def _connect_db(self): # 如果是数据库名链接不上，则创建该名称的数据库，返回的是一个连接实例
try:
con = Connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.dbname)
return con
except Exception as e:
if str(e)[:4] == '1049':
con = Connect(host=self.host, port=self.port, user=self.user, password=self.password)
cur = con.cursor()
cur.execute('CREATE DATABASE %s' % self.dbname)
con = Connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.dbname)
return con
else:
print('MySQL数据库初始化错误，错误信息是：%s' % str(e))
def get_connection(self):
"""
Function name : get_connection()
Return DB object of specified DB reading parameters from conf_vars dictionary
Args : self (reference to the object of class MyDBConnection)
Returns : DB object
Exception : SQL_ERR (write to log)
Exception (write to log)
"""
try:
self.db = Connect(host=self.conf_vars['host'],
port=self.conf_vars['port'],
user=self.conf_vars['user'],
password=self.conf_vars['password'],
database=self.conf_vars['database'])
except SQL_ERR as e:
logger.exception('Exception: %s', str(e))
print e
except Exception as exp:
logger.exception('Exception: %s', str(exp))
print exp
return self.db
def get_all_employees_from_db():
emps = []
try:
#connect to db
db_connection = Connect(user='******',
password='******',
database='training',
port=3306)
#get the db cursor
db_cursor = db_connection.cursor()
#identify the query and execute using cursor
db_cursor.execute("select * from emp_data")
#process data received from cursor
for (empno, name, salary) in db_cursor:
emps.append(
Employee({
"empno": empno,
"name": name,
"daily_salary": salary
}))
#close the cursor and connection
db_connection.close()
db_cursor.close()
except:
print("exception occurred", sys.exc_info())
return emps
def delete_employee_from_db(empno):
try:
#connect to db
db_connection = Connect(user='******',
password='******',
database='training',
port=3306)
#get the db cursor
db_cursor = db_connection.cursor()
#identify the query and execute using cursor
db_cursor.execute("delete from emp_data where empno = %s",
(empno, ))
db_connection.commit()
db_connection.close()
db_cursor.close()
except:
print("Excepion Occurred", sys.exc_info())
return
def add_employee_to_db(new_emp):
try:
#connect to db
db_connection = Connect(user='******',
password='******',
database='training',
port=3306)
#get the db cursor
db_cursor = db_connection.cursor()
#identify the query and execute using cursor
db_cursor.execute(
"insert into emp_data values(%s,%s,%s)",
(new_emp.empno, new_emp.name, new_emp.salary_per_day))
db_connection.commit()
db_connection.close()
db_cursor.close()
except:
new_emp = None
print("Exception Occured", sys.exc_info())
return new_emp
def thread_cmd(server):
cnx = Connect(**server.client_config.copy())
cnx.cmd_query("SELECT USER()")
rows = cnx.get_rows()
print(rows)
cnx.cmd_query("shutdown")
__author__ = 'Administrator'
# 'Administrator' 为预定义的变量，为你当前登录的计算机用户名
from mysql.connector import Connect
mydb = Connect(
host="localhost", # 数据库主机地址
user="******", # 数据库用户名
passwd="123456", # 数据库密码
database="runnoob_db")
mycursor = mydb.cursor()
mycursor.execute("select id, url from sites")
myresult = mycursor.fetchall(3)
for x in myresult:
print(x)
import re
import mysql
import pytest
from mysql.connector import Connect
HOST = 'localhost'
USER = '******'
PSWD = 'root'
DBNAME = 'office'
MYSQL_CONNECTION = Connect(host=HOST, user=USER, passwd=PSWD, db=DBNAME)
class Employee():
TBNAME = 'employees'
CNAMES = {
'name': {
'column': 'name',
'type': 'varchar(30)',
'rules': re.compile('^[a-zA-Z]{2,30}$')
},
'dob': {
'column': 'dob',
'type': 'date',
'rules': re.compile('[0-9]{4}-[0-9]{2}-[0-9]{2}')
},
'salary': {
'column': 'salary',
'type': 'double',
'rules': re.compile('[0-9]{3,10}')
}
