def tweetData():
global mood_lag
if db.port:
cnx = pymysql.connect(charset='utf8', host=db.hostname, port=db.port, user=db.username, passwd=db.password, db=db.path[1:])
else:
cnx = pymysql.connect(charset='utf8', host=db.hostname, user=db.username, passwd=db.password, db=db.path[1:])
cursor = cnx.cursor()
tweets = []
rs = "SELECT text, sentiment, date, lat, lng FROM tweets WHERE `date` > %s ORDER BY `date` DESC LIMIT 1000"
lag = datetime.datetime.now() - datetime.timedelta(minutes=mood_lag)
params = [lag.isoformat(' ')]
cursor.execute(rs, params)
for t, s, d, lat, lng in cursor:
tweet = {}
tweet['text'] = t
tweet['sentiment'] = s
tweet['date'] = d.isoformat(' ')
tweet['lat'] = lat
tweet['lng'] = lng
tweets.append(tweet)
cursor.close()
cnx.close()
return json.dumps(tweets)
def formula_to_dbid(formula_str, backslash_fix=False):
"""
Convert a LaTeX formula to the database index.
Parameters
----------
formula_str : string
The formula as LaTeX code.
backslash_fix : boolean
If this is set to true, then it will be checked if the same formula
exists with a preceeding backslash.
Returns
-------
int :
The database index.
"""
global __formula_to_dbid_cache
if __formula_to_dbid_cache is None:
mysql = utils.get_mysql_cfg()
connection = pymysql.connect(host=mysql['host'],
user=mysql['user'],
passwd=mysql['passwd'],
db=mysql['db'],
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor()
# Get all formulas that should get examined
sql = ("SELECT `id`, `formula_in_latex` FROM `wm_formula` ")
cursor.execute(sql)
formulas = cursor.fetchall()
__formula_to_dbid_cache = {}
for fm in formulas:
__formula_to_dbid_cache[fm['formula_in_latex']] = fm['id']
if formula_str in __formula_to_dbid_cache:
return __formula_to_dbid_cache[formula_str]
elif backslash_fix and ('\\%s' % formula_str) in __formula_to_dbid_cache:
return __formula_to_dbid_cache['\\%s' % formula_str]
else:
logging.info("Symbol '%s' was not found. Add it to write-math.com.",
formula_str)
mysql = utils.get_mysql_cfg()
connection = pymysql.connect(host=mysql['host'],
user=mysql['user'],
passwd=mysql['passwd'],
db=mysql['db'],
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor()
sql = ("INSERT INTO `wm_formula` (`user_id`, `formula_name`, "
"`formula_in_latex`, "
"`mode`, `package`) VALUES ("
"'10', %s, %s, 'bothmodes', NULL);")
if len(formula_str) < 20:
logging.info("Insert formula %s.", formula_str)
cursor.execute(sql, (formula_str, formula_str))
connection.commit()
__formula_to_dbid_cache[formula_str] = connection.insert_id()
return __formula_to_dbid_cache[formula_str]
def realTestPamAuth(self):
db = self.db.copy()
import os
db['password'] = os.environ.get('PASSWORD')
cur = self.connect().cursor()
try:
cur.execute('show grants for ' + TestAuthentication.osuser + '@localhost')
grants = cur.fetchone()[0]
cur.execute('drop user ' + TestAuthentication.osuser + '@localhost')
except pymysql.OperationalError as e:
# assuming the user doesn't exist which is ok too
self.assertEqual(1045, e.args[0])
grants = None
with TempUser(cur, TestAuthentication.osuser + '@localhost',
self.databases[0]['db'], 'pam', os.environ.get('PAMSERVICE')) as u:
try:
c = pymysql.connect(user=TestAuthentication.osuser, **db)
db['password'] = '******'
with self.assertRaises(pymysql.err.OperationalError):
pymysql.connect(user=TestAuthentication.osuser,
auth_plugin_map={b'mysql_cleartext_password': TestAuthentication.DefectiveHandler},
**self.db)
except pymysql.OperationalError as e:
self.assertEqual(1045, e.args[0])
# we had 'bad guess at password' work with pam. Well at least we get a permission denied here
with self.assertRaises(pymysql.err.OperationalError):
pymysql.connect(user=TestAuthentication.osuser,
auth_plugin_map={b'mysql_cleartext_password': TestAuthentication.DefectiveHandler},
**self.db)
if grants:
# recreate the user
cur.execute(grants)
def connect(self):
"""Connects to a database as set in `site_config.json`."""
warnings.filterwarnings('ignore', category=pymysql.Warning)
usessl = 0
if frappe.conf.db_ssl_ca and frappe.conf.db_ssl_cert and frappe.conf.db_ssl_key:
usessl = 1
self.ssl = {
'ca':frappe.conf.db_ssl_ca,
'cert':frappe.conf.db_ssl_cert,
'key':frappe.conf.db_ssl_key
}
conversions.update({
FIELD_TYPE.NEWDECIMAL: float,
FIELD_TYPE.DATETIME: get_datetime,
TimeDelta: conversions[binary_type],
UnicodeWithAttrs: conversions[text_type]
})
if usessl:
self._conn = pymysql.connect(self.host, self.user or '', self.password or '',
charset='utf8mb4', use_unicode = True, ssl=self.ssl, conv = conversions)
else:
self._conn = pymysql.connect(self.host, self.user or '', self.password or '',
charset='utf8mb4', use_unicode = True, conv = conversions)
# MYSQL_OPTION_MULTI_STATEMENTS_OFF = 1
# # self._conn.set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_OFF)
self._cursor = self._conn.cursor()
if self.user != 'root':
self.use(self.user)
frappe.local.rollback_observers = []
def search_results_byrequests(query):
connzsky = pymysql.connect(host=DB_HOST,port=DB_PORT_MYSQL,user=DB_USER,password=DB_PASS,db=DB_NAME_MYSQL,charset=DB_CHARSET,cursorclass=pymysql.cursors.DictCursor)
currzsky = connzsky.cursor()
taginsertsql = 'REPLACE INTO search_tags(tag) VALUES(%s)'
currzsky.execute(taginsertsql,query)
connzsky.commit()
currzsky.close()
connzsky.close()
page=request.args.get('page',1,type=int)
conn = pymysql.connect(host=DB_HOST,port=DB_PORT_SPHINX,user=DB_USER,password=DB_PASS,db=DB_NAME_SPHINX,charset=DB_CHARSET,cursorclass=pymysql.cursors.DictCursor)
curr = conn.cursor()
querysql='SELECT * FROM film WHERE MATCH(%s) ORDER BY requests DESC limit %s,20 OPTION max_matches=1000'
curr.execute(querysql,[query,(page-1)*20])
result=curr.fetchall()
#countsql='SELECT COUNT(*) FROM film WHERE MATCH(%s)'
countsql='SHOW META'
curr.execute(countsql)
resultcounts=curr.fetchall()
counts=int(resultcounts[0]['Value'])
taketime=float(resultcounts[2]['Value'])
curr.close()
conn.close()
pages=(counts+19)/20
tags=Search_Tags.query.order_by(Search_Tags.id.desc()).limit(50)
form=SearchForm()
form.search.data=query
return render_template('list_byrequests.html',form=form,query=query,pages=pages,page=page,hashs=result,counts=counts,taketime=taketime,tags=tags)
def _connect(self, host, port, mysql_sock, user, password, defaults_file):
try:
import pymysql
except ImportError:
raise Exception(
"Cannot import PyMySQl module. Check the instructions "
"to install this module at https://pypi.python.org/pypi/PyMySQL")
if defaults_file != '':
db = pymysql.connect(read_default_file=defaults_file)
elif mysql_sock != '':
db = pymysql.connect(unix_socket=mysql_sock,
user=user,
passwd=password)
elif port:
db = pymysql.connect(host=host,
port=port,
user=user,
passwd=password)
else:
db = pymysql.connect(host=host,
user=user,
passwd=password)
self.log.debug("Connected to MySQL")
return db
def similarity():
#conn = pymysql.connect(host='127.0.0.1', unix_socket='/tmp/mysql.sock', user='******', passwd=None, db='mysql')
conn_yelp = pymysql.connect(host='127.0.0.1', port=3306, user='******', passwd='123456', db='test')
cur_yelp = conn_yelp.cursor()
cur_yelp.execute("SELECT name,location,phone FROM resterant")
infos_of_yelp = []
for row in cur_yelp:
infos_of_yelp.append(row)
cur_yelp.close()
conn_yelp.close()
conn_allmenu = pymysql.connect(host='127.0.0.1', port=3306, user='******', passwd='123456', db='biafinal_db')
cur_allmenu = conn_allmenu.cursor()
cur_allmenu.execute("SELECT rest_name,address,telephone FROM restaurant_info")
infos_of_allmenu = []
for row in cur_allmenu:
infos_of_allmenu.append(row)
cur_allmenu.close()
conn_allmenu.close()
result = []
for info in infos_of_allmenu:
simi = find_most_simi(info,infos_of_yelp)
if simi[1]<2:
print info,simi
result.append(info)
print len(result)
def connDB_yw(server,dbname,port):
if socket.gethostname() == 'hskj-backup250':
conn= pymysql.connect(host=server,port=port,user='******',passwd='20141024',db=dbname,charset='utf8')
else:
conn= pymysql.connect(host='192.168.120.12',user='******',passwd='123456',db='sms_server',charset='utf8')
cur = conn.cursor()
return (conn,cur)
def _connect(self, host, port, mysql_sock, user, password, defaults_file):
try:
import pymysql
except ImportError:
raise Exception("Cannot import pymysql module. Check the instructions "
"to install this module at https://app.datadoghq.com/account/settings#integrations/mysql")
if defaults_file != '':
db = pymysql.connect(read_default_file=defaults_file)
elif mysql_sock != '':
db = pymysql.connect(unix_socket=mysql_sock,
user=user,
passwd=password)
elif port:
db = pymysql.connect(host=host,
port=port,
user=user,
passwd=password)
else:
db = pymysql.connect(host=host,
user=user,
passwd=password)
self.log.debug("Connected to MySQL")
return db
def connDB_plate():
if socket.gethostname() == 'hskj-backup250':
conn= pymysql.connect(host='210.14.134.77',port=13306,user='******',passwd='20141024',db='super_plate',charset='utf8')
else:
conn= pymysql.connect(host='192.168.120.12',user='******',passwd='123456',db='provinces_test_un',charset='utf8')
cur = conn.cursor()
return (conn,cur)
def main(): # Performs getAvg for each item_id in the specified database ( 4223 item_ids in database )
totalValue = 0
passes = 0
db1 = pymysql.connect(credentials.host, credentials.username, credentials.password, "initial_item_list") # DB of item ids
cursor1 = db1.cursor() # creates cursor object
cursor1.execute("SELECT item_id FROM items") # Use as reference list for ids
db3 = pymysql.connect(credentials.host, credentials.username, credentials.password, "primary_scrape_test") # DB to iterate through
cursor3 = db3.cursor()
cursor3.execute("SHOW TABLES")
tables = cursor3.fetchall()
cursor3.execute("SHOW TABLES")
cursor3.execute("SELECT FOUND_ROWS()")
numTables = cursor3.fetchone()
numTables = formatString(numTables)
IDtoUse = cursor1.fetchone()
while IDtoUse is not None:
ID = formatString(IDtoUse)
getAvg(ID, tables, passes, numTables)
IDtoUse = cursor1.fetchone()
def databaseSetup():
try:
conn = pymysql.connect(host='127.0.0.1', port=3306, user='******', passwd='', db='test') # test DEFINATLEY exists
cur = conn.cursor()
cur.execute("CREATE DATABASE IF NOT EXISTS upStatsAPP") # DB name is now upStatsAPP
cur.close()
conn.close() # close test
conn = pymysql.connect(host='127.0.0.1', port=3306, user='******', passwd='', db='upStatsAPP') # test DEFINATLEY exists
cur = conn.cursor() # open the 'newly created' upStatsAPP
cur.execute("DROP TABLE IF EXISTS CarStats")
cur.execute("CREATE TABLE IF NOT EXISTS CarStats(" +
"EventID TEXT, Rev TEXT, Gear TEXT, RPM TEXT, " +
"Speed TEXT, FRrpm TEXT, FLrpm TEXT, " +
"RRrp TEXT, RLrpm TEXT, Suspension1 TEXT, " +
"Suspension2 TEXT, Suspension3 TEXT, Suspension4 TEXT, GForceX TEXT, " +
"GForceY TEXT, AirTemp TEXT, Throttle TEXT, " +
"CoolentTemp TEXT, Battery TEXT, BatteryTemp TEXT, " +
"Lambda TEXT)") #SQL query for table setup
print("Database Reset Successful")
cur.close()
conn.close()
except (Exception):
print("Database Did Not Reset, Something Went Wrong")
def _connect(self, host, port, mysql_sock, user, password, defaults_file):
service_check_tags = [
'host:%s' % host,
'port:%s' % port
]
try:
if defaults_file != '':
db = pymysql.connect(read_default_file=defaults_file)
elif mysql_sock != '':
db = pymysql.connect(unix_socket=mysql_sock,
user=user,
passwd=password)
elif port:
db = pymysql.connect(host=host,
port=port,
user=user,
passwd=password)
else:
db = pymysql.connect(host=host,
user=user,
passwd=password)
self.log.debug("Connected to MySQL")
self.service_check(self.SERVICE_CHECK_NAME, AgentCheck.OK, tags=service_check_tags)
except Exception:
self.service_check(self.SERVICE_CHECK_NAME, AgentCheck.CRITICAL, tags=service_check_tags)
raise
return db
def connect(self):
""" connect database """
try:
self.dbcon = pymysql.connect(
host=self.mysql_url,
user=self.user_name,
passwd=self.passwd,
db=self.DBname,
charset=self.charset)
self.cur = self.dbcon.cursor();
except pymysql.Error as e:
# database not exists, create it
con = pymysql.connect(
host=self.mysql_url,
user=self.user_name,
passwd=self.passwd,
charset=self.charset)
cur = con.cursor()
if cur.execute("CREATE DATABASE IF NOT EXISTS %s DEFAULT CHARSET 'utf8'" % self.DBname):
self.dbcon = pymysql.connect(
host=self.mysql_url,
user=self.user_name,
passwd=self.passwd,
db=self.DBname,
charset=self.charset)
self.cur = self.dbcon.cursor();
else:
raise e
def execute(self, sql, params=None):
self.dbConn = pymysql.connect(host=DB_HOST, user=DB_USER, passwd=DB_PASSWORD, db=DB,
charset=DB_CHARSET,
cursorclass=pymysql.cursors.DictCursor)
with pymysql.connect(host=DB_HOST, user=DB_USER, passwd=DB_PASSWORD, db=DB, charset=DB_CHARSET,
cursorclass=pymysql.cursors.DictCursor) as cursor:
r = DBResult()
try:
if not params:
r.Rows = cursor.execute(sql)
else:
r.Rows = cursor.execute(sql, params)
r.Result = cursor.fetchall() if r.Rows != 0 else []
r.Suc = True
self.dbConn.commit()
except Exception as e:
r.Err = e
print(e, 'execute Error')
self.dbConn.rollback()
try:
self.dbConn.close()
except:
pass
return r
def getConnection(self, nodb=False):
"""
@rtype L{pymysqlConnection}
"""
if not self.startMysqld():
raise RuntimeError("Can't start mysqld server!")
if nodb:
conn = pymysql.connect(unix_socket=self.getSocket(), user=self.getUser(), passwd=self.getPassword())
else:
if self._conn:
return self._conn
conv = pymysql.converters.conversions.copy()
conv[246]=float # convert decimals to floats
conv[10]=str # convert dates to strings
conn = self._conn = pymysql.connect(
unix_socket=self.getSocket(),
user=self.getUser(),
passwd=self.getPassword(),
db=self.getDbName(),
conv=conv
)
self._conn.autocommit(self.getAutocommit())
cur = conn.cursor()
if not self.getAutocommit():
cur.execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED")
cur.close()
return conn
def preparedb():
db = pymysql.connect(host=DB_HOST, user=DB_USER,
passwd=DB_PASS)
cursor = db.cursor()
create_database(cursor)
cursor.close()
db.close()
db = pymysql.connect(host=DB_HOST, user=DB_USER,
passwd=DB_PASS, db=DB_NAME)
cursor = db.cursor()
for name, ddl in TABLES.iteritems():
try:
cursor.execute(ddl)
except Exception as e:
if e[0] == 1050:
if DEBUG:
print("aiadb: already exists.")
else:
raise e
cursor.close()
db.close()
def opendb(name):
"""Open the database with the name given"""
dbc = dbcredentials.DBcredfile()
creds = dbc.get(name)
if creds.login is None:
return pymysql.connect(host=creds.host, user=creds.user, passwd=creds.password, db=creds.database)
# We need SSH tunnel, first see if we've got one already
try:
return pymysql.connect(host='localhost', port=int(creds.localport), user=creds.user, passwd=creds.password, db=creds.database)
except pymysql.OperationalError as e:
if e.args[0] != 2003:
raise dbopsError("Could not connect to database error was " + e.args[1])
if os.fork() == 0:
os.execvp("ssh", ["ssh", "-nNT", "-L", "%d:localhost:%d" % (int(creds.localport), int(creds.remoteport)), "%s@%s" % (creds.login, creds.host)])
sys.exit(255)
time.sleep(5)
try:
return pymysql.connect(host='localhost', port=int(creds.localport), user=creds.user, passwd=creds.password, db=creds.database)
except pymysql.OperationalError as e:
raise dbopsError("Could not connect to database after SSH tunnel error was " + e.args[1])
def connect_mysql(instance, role='admin'):
"""Connect to a MySQL instance as admin
Args:
hostaddr - object describing which mysql instance to connect to
role - a string of the name of the mysql role to use. A bootstrap role can
be called for MySQL instances lacking any grants.
Returns:
a connection to the server as administrator
"""
log.info('Connecting to {}, please be patient. Timeout is 30 seconds.'.format(instance))
if role == 'bootstrap':
socket = host_utils.get_cnf_setting('socket', instance.port)
username = '******'
password = ''
# when super_read_only is enabled and autocommit is false, `change master to` is not allowed to execute
# so we have to enable autocommit.
db = pymysql.connect(unix_socket=socket,
user=username,
passwd=password,
cursorclass=pymysql.cursors.DictCursor)
else:
username, password = get_mysql_user_for_role(role)
db = pymysql.connect(host=instance.hostname,
port=instance.port,
user=username,
passwd=password,
cursorclass=pymysql.cursors.DictCursor,
connect_timeout=CONNECT_TIMEOUT)
return db
def similarity():
conn_yelp = pymysql.connect(host='127.0.0.1', port=3306, user='******', passwd='123456', db='biafinal_db')
cur_yelp = conn_yelp.cursor()
cur_yelp.execute("SELECT name,location,phone,id FROM restaurant_yelp where location like '%Hoboken%'")
infos_of_yelp = []
for row in cur_yelp:
infos_of_yelp.append(row)
cur_yelp.close()
conn_yelp.close()
conn_allmenu = pymysql.connect(host='127.0.0.1', port=3306, user='******', passwd='123456', db='biafinal_db')
cur_allmenu = conn_allmenu.cursor()
cur_allmenu.execute("SELECT rest_name,address,telephone,rest_id as id FROM restaurant_info")
infos_of_allmenu = []
for row in cur_allmenu:
infos_of_allmenu.append(row)
cur_allmenu.close()
conn_allmenu.close()
result = []
for info in infos_of_allmenu:
simi = find_most_simi(info,infos_of_yelp)
if simi[1]<2:
#print "======",simi[0][3],info[3],"--------";
addNewResterant(simi[0][3],info[3])
#print info,simi
result.append(info)
print len(result)
def mysql_test(options):
"""Returns True if successful, False if unsuccessful."""
status = True
try:
pymysql.connect(
host="localhost",
user=options.app_username,
passwd=options.app_password,
db=options.database,
)
except pymysql.err.OperationalError:
status = False
LOG.debug("Could not connect as app user.")
try:
pymysql.connect(
host="localhost",
user=options.admin_username,
passwd=options.admin_password,
db=options.database,
)
except pymysql.err.OperationalError:
status = False
LOG.debug("Could not connect as admin user.")
return status
def connect(self):
rw = self.rw
try:
import pymysql as MySQLdb
import pymysql.converters
conv_dict = pymysql.converters.conversions.copy()
conv_dict[pymysql.constants.FIELD_TYPE.DECIMAL] = pymysql.converters.convert_float
conv_dict[pymysql.constants.FIELD_TYPE.NEWDECIMAL] = pymysql.converters.convert_float
pymysql.converters.encoders[np.float64] = pymysql.converters.escape_float
pymysql.converters.encoders[np.float32] = pymysql.converters.escape_float
MySQLdb.converters = pymysql.converters
_sqlcompress = False # compression not supported by pymysql yet
except:
import MySQLdb
import MySQLdb.converters
conv_dict = MySQLdb.converters.conversions.copy()
conv_dict[246] = float # Convert Decimal fields to float automatically
_sqlcompress = True
if rw:
self.db = MySQLdb.connect(host = GAVRTDB.host, port=GAVRTDB.port ,user=GAVRTDB.write_user,passwd=GAVRTDB.write_passwd,db=GAVRTDB.db,conv=conv_dict,compress=_sqlcompress)
else:
self.db = MySQLdb.connect(host = GAVRTDB.host, port=GAVRTDB.port ,user=GAVRTDB.read_user,passwd=GAVRTDB.read_passwd,db=GAVRTDB.db,conv=conv_dict,compress=_sqlcompress)
self.c = self.db.cursor()
def search(self):
searchItem = None
tutorList = []
if len(self.course.get()) > 0 and len(self.keyword.get()) > 0:
showwarning("ERROR","Please only enter one search criteria.")
return
elif len(self.course.get()) > 0 and len(self.keyword.get()) == 0:
searchItem = self.course.get()
db = pymysql.connect(host = "academic-mysql.cc.gatech.edu" , passwd = "a1Rlxylj" , user ="******",
db='cs4400_Group36')
c = db.cursor()
query = "SELECT C.courseCode, C.courseTitle, S.name, S.email FROM courseSection C, tutorsFor T, student S WHERE C.courseTitle = T.courseTitle AND T.tutorUsername = S.username AND C.courseCode LIKE '%" + searchItem + "%' GROUP BY T.courseTitle"
c.execute(query)
items = c.fetchall()
for i in items:
tutorList += [[i[0],i[1],i[2],i[3]]]
c.close()
db.commit()
db.close()
elif len(self.course.get()) == 0 and len(self.keyword.get()) > 0:
searchItem = self.keyword.get()
db = pymysql.connect(host = "academic-mysql.cc.gatech.edu" , passwd = "a1Rlxylj" , user ="******",
db='cs4400_Group36')
c = db.cursor()
query = "SELECT C.courseCode, C.courseTitle, S.name, S.email FROM courseSection C, student S, tutorsFor T WHERE S.username = T.tutorUsername AND T.courseTitle = C.courseTitle AND T.courseTitle LIKE '%" + searchItem + "%' GROUP BY T.courseTitle"
c.execute(query)
items = c.fetchall()
for i in items:
tutorList += [[i[0],i[1],i[2],i[3]]]
c.close()
db.commit()
db.close()
else:
showwarning("ERROR","Please enter a valid search.")
return
if len(tutorList) > 0:
self.frame.grid_remove()
for t in range(len(tutorList)):
if t > 0 and tutorList[t][0] == tutorList[t-1][0]:
Lcode = Label(self.frame, text="")
else:
Lcode = Label(self.frame, text=tutorList[t][0])
Lcode.grid(row=t,column=0,sticky=W)
if t > 0 and tutorList[t][1] == tutorList[t-1][1]:
Ltitle = Label(self.frame, text="")
else:
Ltitle = Label(self.frame, text=tutorList[t][1])
Ltitle.grid(row=t,column=1)
Lhold = Label(self.frame,text="",width=10)
Lhold.grid(row=t,column=2)
Lname = Label(self.frame, text=tutorList[t][2])
Lname.grid(row=t,column=3)
Lemail = Label(self.frame, text=tutorList[t][3])
Lemail.grid(row=t,column=4,sticky=E)
self.frame.grid(row = 2, columnspan = 5)
else:
showwarning("ERROR","Sorry, no tutors to display.\nPlease alter your search criteria\nand try again.")
return
def getDBCursor(): db = urlparse(os.environ['DATABASE_URL']) if db.port: cnx = pymysql.connect(charset='utf8', host=db.hostname, port=db.port, user=db.username, passwd=db.password, db=db.path[1:]) else: cnx = pymysql.connect(charset='utf8', host=db.hostname, user=db.username, passwd=db.password, db=db.path[1:]) cursor = cnx.cursor() return (cursor, cnx)
def __connect(self):
try:
self.cnx=pymysql.connect(user="******", passwd="RRCCpi2DC",
host="127.0.0.1", db="medbox", port=3306)
except:
self.cnx=pymysql.connect(user="******", host="127.0.0.1",
db="medbox", port=3306)
self.cursor=self.cnx.cursor()
def __init__(self):
self.conn_roiboard = pymysql.connect(host=DB_HOST, port=DB_PORT, user=DB_USER,
passwd=DB_PWD, db=DB_NAME)
self.conn_roiboard.autocommit(True)
self.conn_informa = pymysql.connect(host=INFORMA_DB_HOST, port=INFORMA_DB_PORT, user=INFORMA_DB_USER,
passwd=INFORMA_DB_PWD, db=INFORMA_DB_NAME)
self.conn_informa.autocommit(True)
self.taxonomy_cache = defaultdict(dict) # {service_id: {lang1: {word1:[concept1, concept2], word2:[concept1, concept3]}}}
def __init__(self):
try:
self.db = pymysql.connect(host=HOST, user='******', password=PASSWORD, database=DATABASE,
charset='utf8', port=3306)
except:
self.db = pymysql.connect(host=HOST, user=USER, password=PASSWORD_LOCAL, database=DATABASE,
charset='utf8', port=3306)
self.cursor = self.db.cursor()
def getNewConnect(self):
if pySet.PRODUCTION_V == False:
self.conn = mdb.connect('localhost', 'root', 'stuff0645', 'COCA_Coll2');
else:
self.conn = mdb.connect('localhost', 'root', 'stuff0645', 'COCA_collocates');
#print self.conn
self.cursor = self.conn.cursor(mdb.cursors.DictCursor)
def test_issue_34(self):
try:
pymysql.connect(host="localhost", port=1237, user="******")
self.fail()
except pymysql.OperationalError as e:
self.assertEqual(2003, e.args[0])
except Exception:
self.fail()
def _mysql_crack(self, dict):
username = dict.split(':')[0]
password = dict.split(':')[1]
try:
pymysql.connect(host=self.ip, user=username, passwd=password, port=self.port)
return 2
except:
pass
# -*- coding:utf-8 -*-
import pymysql
import pymysql.cursors
from prettytable import PrettyTable
from colorama import init, Fore
import pdb
import sys
#数据库名称
database_name = "house_price_04"
# 打开数据库连接
db = pymysql.connect("localhost",
"root",
"aB123456",
database_name,
charset='utf8mb4')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
def main():
#用于存储查询到包含关键字的小区信息check
global data
data = []
global check_name
count = 10
while count > 1:
#输入要查询的小区名称
check_name = input("请输入小区名称：")
#!/usr/bin/python
import pymysql
pymysql.install_as_MySQLdb()
conn = pymysql.connect(host="localhost",
user="******",
passwd="123456s",
db="test",
port=3306)
cursor1 = conn.cursor()
cursor1.execute("SELECT department_id,department_name " + " FROM departments")
for department_id, department_name in cursor1:
print("%6d %-20s" % (department_id, department_name))
cursor1.close()
import pymysql
data = pymysql.connect(
host="localhost",
user="******",
passwd="Root@123",
)
class Manage_user:
def view_user(self):
lst1 = []
lst2 = []
dic = {}
mycursor = data.cursor()
mycursor.execute('USE blogger')
mycursor.execute(
"SELECT table_name FROM information_schema.tables WHERE table_schema = 'blogger'"
)
for index, table in enumerate(
[tables[0] for tables in mycursor.fetchall()]):
lst1.append(index)
lst2.append(table)
for i, j in zip(lst1, lst2):
dic[i + 1] = j
print(dic)
return dic
class Selecting_user:
def __init__(self, user):
print('Worning reminber to bakeup data')
print('Bakeup data')
print('Bakeup data')
print('Bakeup data')
print('Bakeup data')
print('Bakeup data')
#use this for red text for linux
#print('This is a \033[1;35m test \033[0m!')
ctypes.windll.kernel32.SetConsoleTextAttribute(ctypes.windll.kernel32.GetStdHandle(STD_OUTPUT_HANDLE),
FOREGROUND_RED | FOREGROUND_GREEN | FOREGROUND_BLUE)
#time.sleep(5)
copytradeDB = {"host":"192.168.8.34", "database":"copytrading",
"user":"******", "password":"******", "port":3326, "charset":"utf8"}
dbobj = pymysql.connect(host=copytradeDB['host'], database=copytradeDB['database'], user=copytradeDB['user'],
password=copytradeDB['password'], port=copytradeDB['port'], charset=copytradeDB['charset'])
cursor = dbobj.cursor()
try:
#first edit the name
namedit = pd.read_excel('C:\\Users\\a\\Desktop\\select___from_t_broker.xls',
usecols=["BrokerID", "Name","CompanyName"],
skipfooter=2082,
)
for index, row in namedit.iterrows():
cursor.execute("update t_broker set CompanyName='%s' where BrokerID=%d" % (row["CompanyName"],row["BrokerID"]))
pass
dbobj.commit()
del_broker_data = pd.read_excel("C:\\Users\\a\\Desktop\\全经纪商维护表.xlsx",
usecols=["BrokerID", "Name","Operation（1保留 2合并）"],
# 'upgrade-insecure-requests': 1
}
img_content_list = []
for url in urls:
response = urlopen(Request(url, headers=headers))
time.sleep(1)
# 这里获取的不是单纯图片内容...后续研究吧
img_content_list.append(response.read())
with open('test1', 'wb') as f:
f.write(response.read())
f.flush()
return img_content_list
# step5:根据img_src下载图片插入数据库
conn = pymysql.connect(host="127.0.0.1", user="******", password="******", database="dataCollection", port=3306)
cursor = conn.cursor()
sql = """select good_id, img_src from t_supermall_home_goods_info"""
sql_insert = """insert into t_supermall_hoem_goos_info (goods_id,img) values (%s,%s)"""
try:
cursor.execute(sql)
img_info = cursor.fetchall()
img_id_list = []
img_link_list = []
insert_info = []
for img_url_info in img_info:
img_id_list.append(img_url_info[0])
img_link_list.append(img_url_info[1])
# 下载图片内容
img_content_list = download_img(img_link_list)
# 存储到数据库
def get_conn(**kwargs):
conn = pymysql.connect(**kwargs)
cursor = conn.cursor(pymysql.cursors.DictCursor)
return conn, cursor
import requests
import json
import pymysql
import time, datetime
connection = pymysql.connect(host="127.0.0.1", port=3306, user="******", password="******", db="bigbang-debug")
def ExecSql(sql):
try:
cursor = connection.cursor()
cursor.execute(sql)
connection.commit()
except Exception, e:
print e
def run(url,node_name):
#url = 'http://127.0.0.1:9902'
#node_name = 'bigbang'
data = '{"id":1,"method":"getblockcount","jsonrpc":"2.0","params":{}}'
response = requests.post(url, data=data)
result = json.loads(response.text)
data = '{"id":1,"method":"getblockhash","jsonrpc":"2.0","params":{"height":%d}}' % (result["result"] - 1)
response = requests.post(url, data=data)
result = json.loads(response.text)
block_hash = result["result"][0]
for dev in device:
if tordevices.device_online(dev[4]):
slaves.add_device(slave[1], dev[4])
else:
slaves.add_offline_device(slave[1], dev[4])
showLCD(slave[2], 'Slave connected')
sleep(2)
showLCD('Training program', slave[6] + ' started...')
sleep(10)
defaultLCD()
try:
db = pymysql.connect(config['database']['hostname'],
config['database']['username'],
config['database']['password'],
config['database']['database'])
except:
print("Error: MySQL connection failed, program start failed")
lcd.lcd_clear()
oled.cls()
oled.display()
GPIO.cleanup()
db.close()
rfdevice.cleanup()
rfsend.cleanup()
mqtt.loop.stop()
sys.exit(1)
cursor = db.cursor()
import pymysql
import cgi
conn = pymysql.connect(host='localhost',
user='******',
password='******',
db='mystockmarket')
a = conn.cursor()
print("content-text:text/html\r\n\r\n")
print("<html>")
print("<head>")
print("<title>My Stock Market</title>")
print("</head>")
print("<body>")
print("<form action=CategorySubmit.py>")
print("<center>")
print("<table border=1>")
print("<caption><font size=7> Stock Category Registration </font></caption>")
print(
"<tr><td>Enter Category Name</td><td><input type=text name=cname></td></tr>"
)
print(
"<tr><td>Upload Category Image</td><td><input type= file name=cimg></td></tr>"
)
print(
"<tr><td>Enter Category Description</td><td><textarea name=cdesc rows=4 cols=30></textarea></td></tr>"
)
print("<tr><td><input type=submit></td><td><input type= reset></td></tr>")
print("</table>")
print("</center>")
def get_whmcs_data(order_date_query, group_id_query=None, product_status_query=False):
whmcs_data = {}
try:
# Open database connection
db = pymysql.connect(db_host, db_user, db_pass, db_name)
# prepare a cursor object using cursor() method
cursor = db.cursor()
# execute SQL query using execute() method.
# If Orders query is based on group ID
if group_id_query:
cursor.execute(orders_by_group_query_sql.format(order_date_query, group_id_query))
# Query all orders
else:
cursor.execute(orders_query_sql.format(order_date_query))
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
product_orderid_list = []
for row in results:
order_id = row[0]
ordernum = row[1]
order_date = row[2]
order_invoiceid = row[3]
order_status = row[4]
order_amount = row[5]
client_id = row[6]
client_firstname = row[7]
client_lastname = row[8]
client_companyname = row[9]
client_email = row[10]
multi_products = False
whmcs_data[row[0]] = {
'order_id': str(order_id),
'ordernum': str(ordernum),
'order_date': str(order_date),
'order_invoiceid': str(order_invoiceid),
'order_status': str(order_status),
'order_amount': str(order_amount),
'client_id': str(client_id),
'client_firstname': str(client_firstname),
'client_lastname': str(client_lastname),
'client_companyname': str(client_companyname),
'client_email': str(client_email),
'multi_products': multi_products
}
# If product_status_query is set to True, Query all the products assigned to this order
if product_status_query:
cursor.execute(product_status_query_sql.format(order_id))
product_status_results = cursor.fetchall()
# Check if results are returned or the product is deleted
if product_status_results:
for product_status_row in product_status_results:
product_id = product_status_row[0]
product_domain = product_status_row[1]
product_domainstatus = product_status_row[2]
product_orderid = product_status_row[3]
# Check if the product ID is found in the product_orderid_list
# To verify if the order has multiple products or not
if product_orderid in product_orderid_list:
product_id = "Multiple Product IDs, please check manually"
product_domain = "Multiple Product Domains, please check manually"
product_domainstatus = "Multiple Status, please check manually"
multi_products = True
# Save the product ID in the list to search it later in the above condition
# to make sure the order has multiple products or not
product_orderid_list.append(product_orderid)
whmcs_data[row[0]].update(
{
'product_id': product_id,
'product_domain': product_domain,
'product_domainstatus': product_domainstatus,
'multi_products': multi_products
}
)
print("============================")
else:
print("Not found {}".format(product_status_results))
whmcs_data[row[0]].update(
{
'product_id': None,
'product_domain': None,
'product_domainstatus': None
}
)
return whmcs_data
except Exception as e:
print("Exception: {}".format(e))
import pymysql
import getInfo_grequests
import os
import json
import time
conn=pymysql.connect(host='212.64.6.2',port=3306,user='******',passwd='dzt',db='sov',charset="utf8")
cursor=conn.cursor()
def GetUserId(i):
sql='select user_id from comment where id between'
cursor.execute(sql+' {} and {}'.format(i*1000,(i+1)*1000))
result=cursor.fetchall()
result=tuple(set(result))
return result
print(result)
# for i in result:
# Info=getInfo.GetUserInfo(i[0])
# print(i,Info)
def getUserInfos(i):
result=GetUserId(i)
step = 100
UserIdsGroup =[result[i:i+step] for i in range(0,len(result),step)]
AllUserInfo=[]
for UserIds in UserIdsGroup:
UserIds=[i[0] for i in UserIds]
texts=getInfo_grequests.getText(UserIds)
for i in range(len(UserIds)):
UserInfo=getInfo_grequests.GetUserInfo(texts[i])
print(i,UserInfo)
if UserInfo!=0:
# -*- coding: utf-8 -*-
"""
Created on Wed Jul 19 17:15:49 2017
@author: root
"""
import requests
from scrapy.selector import Selector
import pymysql
conn = pymysql.connect(host="10.65.1.62",
user="******",
passwd="0114",
db="proxy_ip",
charset="utf8")
cursor = conn.cursor()
def crawl_ips():
get = GetIP()
#爬取西刺的免费ip代理
headers = {
"User-Agent":
"Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101 Firefox/52.0"
}
for i in range(1568):
re = requests.get("http://www.xicidaili.com/nn/{0}".format(i),
headers=headers)
selector = Selector(text=re.text)
from pandas import Series, DataFrame
import time
from getStockID import sid, name, market, coe
from headers import header
start = time.time()
starttime = int(time.strftime("%M", time.localtime()))
yesterday = datetime.datetime.now().strftime("%Y%m%d")
month = [20171231]
l = 0
for mon in range(len(month)):
for l in range(len(sid)):
print l
db = pymysql.connect(host='60.249.6.104',
port=33060,
user='******',
passwd='ncutim',
db='Listing',
charset='utf8')
cursor = db.cursor()
asid = sid[l]
aname = name[l]
amarket = market[l]
acoe = coe[l]
params = {"date": month[mon], "stockNo": asid}
conntime = b = int(time.strftime("%M", time.localtime()))
proxiesList = [
"http://60.249.6.104:8080", "http://60.249.6.105:8080",
"http://60.249.6.104:8080", "http://192.168.1.3:8080"
] * 1000
if conntime - starttime >= 1:
headers = {'user-agent': header[l]}
# -*- coding: UTF-8 -*-
from urllib import request
import chardet
import re
import pymysql
if __name__ == "__main__":
# 打开数据库连接
db = pymysql.connect(host="localhost",
user="******",
password="******",
db="graduationprojectyan",
port=3306,
charset="utf8")
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
#取得html
req = request.Request(
"http://xueshu.baidu.com/usercenter/data/journal?query=&category=4%2C0&journal_db=4&journal_name=%E4%B8%AD%E5%9B%BD%E7%A7%91%E6%8A%80%E6%A0%B8%E5%BF%83%E6%9C%9F%E5%88%8A&page=3"
)
response = request.urlopen(req)
html = response.read()
charset = chardet.detect(
html) #判断网页的编码并以字典方式返回---charset是字典格式的，当中有encoding
html = html.decode(charset['encoding'])
#进行正则匹配
pattern = re.compile('<div class="journal_right">\n.*?<a.*?</a>')
items = re.findall(pattern, html)
for item in items:
temp = item.replace('\n', '')
temp = temp.replace(' ', '')
def query_all_stock_profit_data():
connection = pymysql.connect(db='stock_quant',
user='******',
password='******',
host='127.0.0.1',
port=3306,
charset='utf8')
engine = create_engine(
'mysql+pymysql://root:root@localhost:3306/stock_quant?charset=utf8')
cursor = connection.cursor()
cursor.execute("select * from bs_stock_basic where type = 1")
result = cursor.fetchall()
quarters = [1, 2, 3, 4]
current_month = datetime.datetime.now().month
current_quarter = math.ceil(current_month / 3)
current_year_quarters = range(1, current_quarter + 1)
current_year = datetime.datetime.now().year
result_profit_list = []
for data in result:
stock_code = data[1]
ipo_date = data[3]
ipo_year = int(ipo_date.split('-')[0])
ipo_month = int(ipo_date.split('-')[1])
ipo_quarter = math.ceil(ipo_month / 3)
ipo_quaters = range(ipo_quarter, 5)
stock_name = data[2]
print(stock_name, ipo_date, ipo_year, ipo_month, ipo_quaters)
for year in range(ipo_year, current_year + 1):
print(year, current_year, ipo_year)
if year == current_year:
#当前季度的
for quarter in current_year_quarters:
profit_list = []
rs_profit = bs.query_profit_data(code=stock_code,
year=year,
quarter=quarter)
while (rs_profit.error_code == '0') & rs_profit.next():
result_profit_list.append(rs_profit.get_row_data())
elif year == ipo_year: #ipo当年的季度
for quarter in ipo_quaters:
profit_list = []
rs_profit = bs.query_profit_data(code=stock_code,
year=year,
quarter=quarter)
while (rs_profit.error_code == '0') & rs_profit.next():
result_profit_list.append(rs_profit.get_row_data())
else:
for quarter in quarters:
profit_list = []
rs_profit = bs.query_profit_data(code=stock_code,
year=year,
quarter=quarter)
while (rs_profit.error_code == '0') & rs_profit.next():
result_profit_list.append(rs_profit.get_row_data())
fields = [
'code', 'pubDate', 'statDate', 'roeAvg', 'npMargin', 'gpMargin',
'netProfit', 'epsTTM', 'MBRevenue', 'totalShare', 'liqaShare'
]
result_profit = pd.DataFrame(result_profit_list, columns=fields)
result_profit.to_sql('bs_stock_profit', engine, index=True)
connection.close()
import pymysql
conn = pymysql.connect(host="localhost",
user="******",
passwd="",
db="eventmanagement")
mycurousr = conn.cursor()
mycurousr.execute("""CREATE TABLE USER(
USER_ID VARCHAR(5) PRIMARY KEY NOT NULL,
USER_PASSWORD VARCHAR(30),
USER_NAME VARCHAR(30),
USER_EMAIL VARCHAR(30),
PHONENUM VARCHAR(12),
USER_TYPE VARCHAR(1)
)
""")
mycurousr.execute("""CREATE TABLE ADMIN(
USER_ID VARCHAR(5) PRIMARY KEY NOT NULL,
FOREIGN KEY(USER_ID) REFERENCES USER(USER_ID)
)
""")
mycurousr.execute("""CREATE TABLE STUDENT(
USER_ID VARCHAR(5) PRIMARY KEY NOT NULL,
STUDENT_GENDER VARCHAR(1),
FOREIGN KEY(USER_ID) REFERENCES USER(USER_ID)
)
""")
def __init__(self, name, host, port):
self.name = name
self.client = pymysql.connect(host=host,user=username,passwd=password,database=name,port=port,charset='utf8')
self.cursor = self.client.cursor() #创建一个游标对象
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# File : demo2.py
# Author: WangYu
# Date : 2020/12/2
import numpy as np
import pandas as pd
import pymysql
import re
#python连接数据库test50，建立游标
db=pymysql.connect(host='localhost',port=3306,user='******',passwd='aptx4869',
db='test',charset='utf8')
cur=db.cursor()
#获取数据库中表列名，顺序维持不乱
def sql2df(name_cur,name_tab_str):
#获取表内数据
sql_data='''select * from %s; '''%name_tab_str
name_cur.execute(sql_data)
data=name_cur.fetchall()
#获取列名
cols=[i[0] for i in name_cur.description]
#sql内表转换pandas的DF
df=pd.DataFrame(np.array(data),columns=cols)
return df
def table_exists(con,table_name): #这个函数用来判断表是否存在
sql = "show tables;"
con.execute(sql)
try:
import pymysql
db = pymysql.connect("host","user","password","database")
cursor = db.cursor()
cursor.execute("show tables")
table = cursor.fetchall()
#print("tables is %s: " % table)
print(table)
db.close()
except Exception as e:
f_log = open("mysql.log","a+",encoding="utf-8")
f_log.writelines(repr(e)+"\n")
f_log.flush()
f_log.close()
def main():
restaurant_data_path = os.path.realpath('restaurant_with_menu.json') # gets the relative path of restaurant_with_menu.json
restaurant_data_file = open(restaurant_data_path, encoding="utf-8") # opening the restaurant_data_path
restaurant_data = json.load(restaurant_data_file) # loading the restaurant_data_path to read it as json
connection_instance = pymysql.connect(host=HOST_NAME, user=USER_NAME, password=USER_PASS, database=DATABASE_NAME) # # enter your database's credentials
cursor_instance = connection_instance.cursor() # creating a cursor instance of connection_instance
try:
create_restaurant_detail_table = "CREATE TABLE restaurant_detail (restaurant_id int NOT NULL , restaurant_name varchar(255) NOT NULL, cash_balance DOUBLE,PRIMARY KEY (restaurant_id))" # query for creating restaurant_detail
cursor_instance.execute(create_restaurant_detail_table) # executing the above query
connection_instance.commit() # committing the above query
create_rest_timingss_table = "CREATE TABLE rest_timingss (id int NOT NULL AUTO_INCREMENT, restaurant_id int NOT NULL, day_num int NOT NULL, opening_time TIME DEFAULT NULL, closing_time TIME DEFAULT NULL, PRIMARY KEY (id), FOREIGN KEY (restaurant_id) REFERENCES restaurant_detail(restaurant_id))" # query for creating rest_timingss
cursor_instance.execute(create_rest_timingss_table) # executing the above query
connection_instance.commit() # committing the above query
create_menu_details = "CREATE TABLE menu_details (menu_id int NOT NULL AUTO_INCREMENT, restaurant_id int NOT NULL, dish_name varchar(1000) NOT NULL, menu_price DOUBLE, PRIMARY KEY (menu_id), FOREIGN KEY (restaurant_id) REFERENCES restaurant_detail(restaurant_id))" # query for creating menu_details
cursor_instance.execute(create_menu_details) # executing the above query
connection_instance.commit() # committing the above query
try:
restaurant_id = 1 # making restaurant_id as 1 and will be inserting this value in the restaurant_id
# column of restaurant_detail
insert_in_restaurant_detail_table = "INSERT INTO restaurant_detail (restaurant_id, restaurant_name, cash_balance) VALUES (%s, %s, %s)" # query for inserting record in restaurant_detail
for details in restaurant_data:
cursor_instance.execute(insert_in_restaurant_detail_table,(restaurant_id, details['restaurantName'], details['cashBalance'])) # inserting the records in the restaurant_detail
connection_instance.commit() # commting the above query
restaurant_id += 1 # incrementing the value of restaurant_id
except: # executes if you are not able to insert record in restaurant_detail table
print("Can't insert record in restaurant_detail table")
try:
restaurant_id = 1 # making restaurant_id as 1 and will be inserting this value in the restaurant_id
# column of menu_details
insert_in_menu_details_table = "INSERT INTO menu_details (restaurant_id, dish_name, menu_price) VALUES (%s, %s, %s)" # query for inserting record in menu_details
for details in restaurant_data:
for food_item in details['menu']:
cursor_instance.execute(insert_in_menu_details_table,(restaurant_id, food_item['dishName'], food_item['price'])) # inserting the records in the menu_details
connection_instance.commit() # committing the above query
restaurant_id += 1 # incrementing the above query
except: # executes if you are not able to insert record in menu_details table
print("Can't insert record in menu_details table")
try:
restaurant_id = 1 # making restaurant_id as 1 and will be inserting this value in the restaurant_id
# column in rest_timingss
insert_in_rest_timingss_table = "INSERT INTO rest_timingss (restaurant_id, day_num, opening_time, closing_time) VALUES (%s, %s, %s, %s)" # query for inserting record in rest_timingss
for details in restaurant_data:
timings_list = splitting_the_opening_hours(details['openingHours'])
for day_index in range(0, 7): # this is to insert the records for each day for each restaurant
if len(timings_list[day_index]) == 2: # this executes when the restaurant is open on the
# particular day
cursor_instance.execute(insert_in_rest_timingss_table, (restaurant_id, day_index, timings_list[day_index][0], timings_list[day_index][1])) # inserting records in the rest_timingss
connection_instance.commit() # committing the above query
else: # this executes when the restaurant is not open on the particular day
cursor_instance.execute(insert_in_rest_timingss_table, (restaurant_id, day_index, -1, -1))
# inserting records in the rest_timingss and adding -1 in opening and closing time to signify
# that it is closed on that particular day
connection_instance.commit() # committing the above query
restaurant_id += 1 # incrementing the restaurant_id
print("Tables created successfully")
except: # executes when we can't insert record in rest_timingss
print("Can't insert record in rest_timingss table")
except: # executes when we can't create tables
print("Can't create tables")
finally:
restaurant_data_file.close()
connection_instance.close()
import pymysql
import csv
import goslate
import cPickle
import json
dbname = "cped"
host = "localhost"
user = "******"
passwd = ""
db = pymysql.connect(db=dbname,
host=host,
user=user,
passwd=passwd,
charset='utf8')
cur = db.cursor()
sql_create_urllist = '''CREATE TABLE IF NOT EXISTS urllist
(id INTEGER,
namePINYIN VARCHAR(256),
nameCHN VARCHAR(256),
url VARCHAR(256),
PRIMARY KEY (id));'''
sql_create_infolist = '''CREATE TABLE IF NOT EXISTS infolist
(id INTEGER,
sex VARCHAR(128),
ethnicity VARCHAR(256),
edu VARCHAR(256),
studying_abroad VARCHAR(256),
studying_abroad_country VARCHAR(256),
birthdate VARCHAR(256),
deathdate VARCHAR(256),
import pymysql
import os
conn = pymysql.connect(host='localhost',
user=os.environ.get('C9_USER'),
password='',
database='classicmodels')
cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = """
select `firstName`,`lastName`,`city` from
employees join offices ON `employees`.`officeCode`=`offices`.`officeCode`
"""
cursor.execute(sql)
for each_employee in cursor:
print(each_employee)
def __init__(self):
# in local change to self.connection = pymysql.connect("localhost","root","valaparla","face" ) depends on in config
self.connection = pymysql.connect("localhost","root","valaparla","face" )
def ceshi(request):
import pymysql
conn = pymysql.connect(host='yunwei.51rz.com', user='******', passwd='KO;&czy@ghc5}$*gzr$v0if*', db='wd',
port=13600, charset="utf8")
cursor = conn.cursor()
cursor.execute("""SELECT h.*
from
(
SELECT a.uid,a.recover_account,a.recover_times,b.create_time,a.id
from 05b_2list_recover a
INNER JOIN rz_cg_queue_prod b on a.bid=b.bid
where b.type=0
and b.create_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59')
and a.recover_times > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59')
and a.recover_status in (0,1)
UNION ALL
SELECT a.user_id,a.repayment_amount,a.repayment_time,a.full_time,a.id
from
(
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_0 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_1 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_2 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_3 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_4 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_5 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_6 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_7 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_8 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_9 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_10 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_11 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_12 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_13 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_14 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_15 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_16 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_17 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_18 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_19 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_20 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_21 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_22 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_23 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_24 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_25 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_26 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_27 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_28 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_29 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_30 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_31 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id where a.`status` in (0,1) and b.product_id<>10020 and b.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59')
) a
UNION ALL
SELECT a.user_id,a.repayment_amount,a.repayment_time,a.full_time,a.id
from
(
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_0 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_1 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_2 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_3 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_4 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_5 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_6 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_7 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_8 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_9 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_10 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_11 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_12 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_13 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_14 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_15 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_16 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_17 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_18 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_19 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_20 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_21 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_22 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_23 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_24 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_25 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_26 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_27 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_28 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_29 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_30 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') UNION ALL
SELECT a.*,b.full_time from new_wd.rz_borrow_collection_31 a INNER JOIN new_wd.rz_borrow_big b on a.big_borrow_id=b.id INNER JOIN new_wd.rz_borrow c on a.borrow_id=c.id where a.`status` in (0,1) and b.product_id=10020 and c.full_time <= CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59') and a.repayment_time > CONCAT(DATE_SUB(CURDATE(),INTERVAL 1 day),' 23:59:59')
) a
) h
INNER JOIN 01u_0info c on h.uid = c.uid
where c.uid_kefu not in (145854,73170,73195,73721,112103,244848,276009,304525,1,181135,757996,910859)
and h.uid not in (740,181,827,1008,1444,1451,1435,1452,6420,7127,11336,11350,11353,11871,12135,5528,18710,19104,19103,27632,6094,12668,14288)""")
data = cursor.fetchall()
col_names = [i[0] for i in cursor.description]
info_list = [dict(zip(col_names, row)) for row in data]
with open("/root/user_recover.txt", "w", encoding="utf-8") as f:
f.write("uid\trecover_account\trecover_times\tcreate_time\tid\n")
for item in info_list:
f.write("%s\t%s\t%s\t%s\t%s\n" % (
item["uid"], item["recover_account"], item["recover_times"], item["create_time"], item["id"]))
return HttpResponse("ok!")
import json
import sys
from bz2 import BZ2File
import string
import pymysql
try:
os.reload(sys)
sys.setdefaultencoding("utf-8")
except:
pass
conn = pymysql.connect(host="localhost", port=3306, user="******", passwd="root", db="wikidata5w", charset="utf8")
cur = conn.cursor()
def main():
file_path = r"E:\WikiData\latest-all.json.bz2"
Get_Json(file_path)
cur.close()
conn.close()
# entities表的插入操作：
def insert_entities(i, t):
try:
print("------------------------------------------------")
sql_insert_entities = 'insert into web_main(entity_id,type,data_type,len,lable,description,aliase,site,title,badges)values(%(entity_id)s,%(type)s,%(data_type)s,%(len)s,%(label)s,%(description)s,%(aliase)s,%(site)s,%(title)s,%(badges)s)'
params = {'entity_id': t['id'],
'type': t['type'],
'data_type': t['datatype'] if t['type'] == 'property' else None,
from selenium import webdriver
from selenium.common.exceptions import TimeoutException
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.action_chains import ActionChains
import pymysql
import os
from nltk.corpus import stopwords
from nltk.tokenize import word_tokenize
import string
import re
#connect to mysql
db = pymysql.connect("localhost", "root", "", "reviewer")
cursor = db.cursor()
#open csv file
file = open(os.path.expanduser(r"~/Desktop/Agoda Reviews.csv"), "wb")
file.write(
b"Review,Rating Date,Rating " + b"\n")
#extract agoda and insert to database
def agoda():
browser = webdriver.Chrome()
browser.get('https://www.agoda.com/taal-vista-hotel/hotel/tagaytay-ph.html')
try:
WebDriverWait(browser, 50).until(EC.visibility_of_element_located((By.XPATH, "//*[@data-selenium='reviews-comments']")))
except TimeoutException:
print("Timed out! Waiting for page to load")
browser.quit()
import pymysql
import serial
import time
import sys
ser = serial.Serial('/dev/ttyS0', 9600, timeout = 1)
conn = pymysql.connect("localhost", "root", "", "testdb")
cur = conn.cursor()
sleeptime = 0
notify = 1
dic = {'ADX': None, 'ADY':None, 'ADZ':None}
s = ["l","i","o","s","r", "p","d", "w"];
index = 6
def insertData():
print("ready to insert")
insert_sql = "insert into doorAcc(ADX, ADY, ADZ) VALUES ('%f', '%f', '%f')" %\
(dic['ADX'], dic['ADY'], dic['ADZ'])
try:
cur.execute(insert_sql)
conn.commit()
print("insert success")
except:
conn.rollback()
def accdataformate(data):
index1 = data.find(',', 1)
if index1 != -1:
index2 = data.find(',', index1 + 1)
from datetime import datetime
start_time=str(datetime.now())
#mariaDB info
db_host = '10.114.0.121'
db_user = '******'
db_pass = '******'
db_name = 'AirKorea_parks'
tb_name = 'hourly_vc'
#base directory
drbase = '/home/guitar79/AK/'
#query_file='sql.txt'
#db connect
conn= pymysql.connect(host=db_host, user=db_user, password=db_pass, db=db_name,\
charset='utf8mb4', local_infile=1, cursorclass=pymysql.cursors.DictCursor)
cur = conn.cursor()
#delete all data in the table
print("TRUNCATE TABLE %s;" %(tb_name))
cur.execute("TRUNCATE TABLE %s;" %(tb_name))
cur.close ()
conn.commit()
finish_rows = 0
#read the list of csv files
for i in sorted(os.listdir(drbase)):
#read csv files
if i[-4:] == '.csv':
read_file = open(drbase+i,'r')
raw_lists = read_file.read()
#!/usr/bin/env python3
import pymysql
conn = pymysql.connect(host='<hostname>',
user='******',
password='******',
db='pymysql_db',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
with conn:
cur = conn.cursor()
cur.execute('SELECT * FROM cities;')
rows = cur.fetchall()
for row in rows:
print(f"{row['cid']} {row['name']} {row['population']}")
def __init__(self):
self.url = 'https://api.ratingdog.cn/v1/search?'
self.db = pymysql.connect(host='127.0.0.1', port=3306, user='******', password='******', database='hebeidb',
charset='utf8')
self.cursor = self.db.cursor()