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)
Example #2
0
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]
Example #3
0
 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)
Example #4
0
	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 = []
Example #5
0
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)
Example #6
0
    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
Example #7
0
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)
Example #8
0
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)
Example #9
0
    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
Example #10
0
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)
Example #11
0
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")
Example #13
0
    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
Example #14
0
 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
Example #15
0
    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
Example #16
0
    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
Example #17
0
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])
Example #19
0
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
Example #20
0
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)
Example #21
0
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
Example #22
0
    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()
Example #23
0
    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)
Example #25
0
 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()
Example #26
0
 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)
Example #29
0
 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()
Example #30
0
 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
Example #31
0
# -*- 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("请输入小区名称:")
Example #32
0
#!/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()
Example #33
0
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):
Example #34
0
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)
    # 存储到数据库
Example #36
0
def get_conn(**kwargs):
    conn = pymysql.connect(**kwargs)
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    return conn, cursor
Example #37
0
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]
Example #38
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))
Example #41
0
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:
Example #42
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)
Example #43
0
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()
Example #46
0
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)
)
""")
Example #47
0
 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() #创建一个游标对象
Example #48
0
#!/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)
Example #49
0
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()
Example #51
0
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),
Example #52
0
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" )
Example #54
0
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!")
Example #55
0
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,
Example #56
0
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()
Example #57
0
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()
Example #59
0
#!/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()