Python操作Oracle、Mysql及文本进行数据处理

最新推荐文章于 2022-12-28 09:33:16 发布
最新推荐文章于 2022-12-28 09:33:16 发布
阅读量1.9k 收藏 3
点赞数
分类专栏： Python Solution 文章标签： python oracle mysql 数据 脚本
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/babyfish13/article/details/53032591
版权


1、脚本中中到的表结构信息

CREATE TABLE `static_sdk_room_messge_send` (
  `xappkey` varchar(100) DEFAULT NULL,
  `roomid` varchar(100) DEFAULT NULL,
  `messge_send_cnt` bigint(20) DEFAULT NULL,
  `pt` varchar(16) DEFAULT NULL,
  `static_date` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `static_sdk_room_messge_send_x` (
  `xappkey` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `roomid` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `messge_send_cnt` bigint(20) DEFAULT NULL,
  `pt` varchar(16) CHARACTER SET utf8 DEFAULT NULL,
  `static_date` varchar(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- Create table
create table STATIC_SDK_ROOM_MESSGE_SEND_X
(
  XAPPKEY         VARCHAR2(100),
  ROOMID          VARCHAR2(100),
  MESSGE_SEND_CNT INTEGER,
  PT              VARCHAR2(16),
  STATIC_DATE     VARCHAR2(10)
)
tablespace TV
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

2、Oracle连接
-----------------------------
db_oracle_oper_ex.py

# -*- coding=utf-8 -*-
import cx_Oracle
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil

warnings.filterwarnings("ignore")


today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 


ora_conn = cx_Oracle.connect('c##tv/c##tv@172.16.96.31:1521/orcl')
cursor = ora_conn.cursor ()

sql_text = "SELECT max(roomid) from static_sdk_room_messge_send_x"
cursor.execute(sql_text)
row = cursor.fetchone()

sql_text="insert into static_sdk_room_messge_send_x(xappkey,roomid,messge_send_cnt,pt,static_date)" \
"VALUES ('3d40e812075a0527','9233097',12,'2016-10-19.08','2016-11-01')"
print sql_text 
cursor.execute(sql_text)



ora_conn.commit()
cursor.close()
ora_conn.close()

-----------------------------
db_ora.py
# -*- coding=utf-8 -*-
import cx_Oracle

db=cx_Oracle.connect('c##tv','c##tv','172.16.96.31:1521/orcl')
print db.version

db.close()

3、Mysql导出成文本文件
-----------------------------
mysql2file.py

# -*- coding=utf-8 -*-
import smtplib
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil

warnings.filterwarnings("ignore")

db_config = {
    'host': '172.16.96.34',
    'user': 'root',
    'passwd': '123',
    'port': 3306,
    'db': 'tableau_market'
}

def getDB():
    try:
        conn = MySQLdb.connect(host=db_config['host'],user=db_config['user'],passwd=db_config['passwd'],port=db_config['port'])
        conn.autocommit(True)
        curr = conn.cursor()
        curr.execute("SET NAMES utf8");
        curr.execute("USE %s" % db_config['db']);
    
        return conn, curr
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

conn,curr = getDB()

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 

os.remove("/root/pystudy/data/static_sdk_room_messge_send_%s.dat" % (yesterday))

sql_text = "select count(*) from static_sdk_room_messge_send where static_date='%s';" % (yesterday)
curr.execute(sql_text)
chg = curr.fetchall()
for chg_num in chg:
    chg_num0 = chg_num[0]
    print chg_num0

    if (chg_num0 >= 1):
      sql_text = "select xappkey,roomid,messge_send_cnt,pt,static_date from static_sdk_room_messge_send where static_date='%s';" % (yesterday)
      curr.execute(sql_text)
      for item_room_message_send in curr.fetchall():
        #item_room_message_sends = curr.fetchall()
        #for item_room_message_send in item_room_message_sends:
        xappkey = item_room_message_send[0]
        roomid = item_room_message_send[1]
        messge_send_cnt = item_room_message_send[2]
        pt = item_room_message_send[3]
        static_date = item_room_message_send[4]
                
        #f=open("/root/pystudy/data/static_sdk_room_messge_send.dat",'a+')
        f=open("/root/pystudy/data/static_sdk_room_messge_send_%s.dat" % (yesterday) ,'a+')
        f.write(xappkey+'\t'+roomid+'\t'+str(messge_send_cnt)+'\t'+pt+'\t'+static_date+'\t\n')
        f.close()
    else:
        print "%s,no data !" % (yesterday)

curr.close()
conn.close()

4、Mysql导到另一个库的Mysql
-----------------------------
mysql2mysql.py

# -*- coding=utf-8 -*-
import smtplib
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil

warnings.filterwarnings("ignore")

db_config_src = {
    'host': '172.16.96.34',
    'user': 'root',
    'passwd': '123',
    'port': 3306,
    'db': 'tableau_market'
}

def get_srcDB():
    try:
        src_conn = MySQLdb.connect(host=db_config_src['host'],user=db_config_src['user'],passwd=db_config_src['passwd'],port=db_config_src['port'])
        src_conn.autocommit(True)
        src_curr = src_conn.cursor()
        src_curr.execute("SET NAMES utf8");
        src_curr.execute("USE %s" % db_config_src['db']);
    
        return src_conn, src_curr
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

src_conn,src_curr = get_srcDB()

db_config_target = {
    'host': '172.16.96.34',
    'user': 'root',
    'passwd': '123',
    'port': 3306,
    'db': 'tableau_market'
}

def get_targetDB():
    try:
        target_conn = MySQLdb.connect(host=db_config_target['host'],user=db_config_target['user'],passwd=db_config_target['passwd'],port=db_config_target['port'])
        target_conn.autocommit(True)
        target_curr = target_conn.cursor()
        target_curr.execute("SET NAMES utf8");
        target_curr.execute("USE %s" % db_config_target['db']);
    
        return target_conn, target_curr
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

target_conn,target_curr = get_targetDB()

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 

select_sql_text = "select count(*) from static_sdk_room_messge_send where static_date='%s';" % (yesterday)
src_curr.execute(select_sql_text)
chg = src_curr.fetchall()
for chg_num in chg:
    chg_num0 = chg_num[0]
    print chg_num0

    if (chg_num0 >= 1):
      select_sql_text = "select xappkey,roomid,messge_send_cnt,pt,static_date from static_sdk_room_messge_send where static_date='%s';" % (yesterday)
      src_curr.execute(select_sql_text)

      delete_sql_text = "delete from static_sdk_room_messge_send_x where static_date='%s';" % (yesterday)
      target_curr.execute(delete_sql_text)

      i = 0
      insert_sql_text = "insert into static_sdk_room_messge_send_x(xappkey,roomid,messge_send_cnt,pt,static_date) values "

      for item_room_message_send in src_curr.fetchall():
        xappkey = item_room_message_send[0]
        roomid = item_room_message_send[1]
        messge_send_cnt = item_room_message_send[2]
        pt = item_room_message_send[3]
        static_date = item_room_message_send[4]
    
        i += 1

        insert_sql_text = insert_sql_text + "( '%s','%s',%d,'%s','%s')," % (xappkey,roomid,messge_send_cnt,pt,static_date)

        print i
        if (i%5000 == 0):
            insert_sql_text = insert_sql_text[0:-1] + ";"    
            target_curr.execute(insert_sql_text)
            #print insert_sql_text

            insert_sql_text = "insert into static_sdk_room_messge_send_x(xappkey,roomid,messge_send_cnt,pt,static_date) values "

      insert_sql_text = insert_sql_text[0:-1] + ";"    
      target_curr.execute(insert_sql_text)

      print i
                    
    else:
        print "%s,no data !" % (yesterday)

src_curr.close()
src_conn.close()
target_curr.close()
target_conn.close()

5、Mysql导到Oracle
-----------------------------
mysql2oracle_no_rpt_err.py

# -*- coding=utf-8 -*-
import cx_Oracle
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil

warnings.filterwarnings("ignore")

db_config_src = {
    'host': '172.16.96.34',
    'user': 'root',
    'passwd': '123',
    'port': 3306,
    'db': 'tableau_market'
}

def get_srcDB():
    try:
        src_conn = MySQLdb.connect(host=db_config_src['host'],user=db_config_src['user'],passwd=db_config_src['passwd'],port=db_config_src['port'])
        src_conn.autocommit(True)
        src_curr = src_conn.cursor()
        src_curr.execute("SET NAMES utf8");
        src_curr.execute("USE %s" % db_config_src['db']);
    
        return src_conn, src_curr
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

src_conn,src_curr = get_srcDB()


target_conn = cx_Oracle.connect('c##tv/c##tv@172.16.96.31:1521/orcl')
target_curr = target_conn.cursor()


today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 

select_sql_text = "select count(*) from static_sdk_room_messge_send where static_date='%s';" % (yesterday)
src_curr.execute(select_sql_text)
chg = src_curr.fetchall()
for chg_num in chg:
    chg_num0 = chg_num[0]
    print chg_num0

    if (chg_num0 >= 1):
      select_sql_text = "select xappkey,roomid,messge_send_cnt,pt,static_date from static_sdk_room_messge_send where static_date='%s';" % (yesterday)
      src_curr.execute(select_sql_text)

      delete_sql_text = "delete from static_sdk_room_messge_send_x where static_date='%s'" % (yesterday)
      target_curr.execute(delete_sql_text)
      target_conn.commit()

      i = 0
      insert_sql_text = "insert into static_sdk_room_messge_send_x(xappkey,roomid,messge_send_cnt,pt,static_date) "

      for item_room_message_send in src_curr.fetchall():
        xappkey = item_room_message_send[0]
        roomid = item_room_message_send[1]
        messge_send_cnt = item_room_message_send[2]
        pt = item_room_message_send[3]
        static_date = item_room_message_send[4]
    
        i += 1

        insert_sql_text = insert_sql_text + " select '%s','%s',%d,'%s','%s' from dual union all " % (xappkey,roomid,messge_send_cnt,pt,static_date)

        print i
        if (i%500 == 0):
            insert_sql_text = insert_sql_text[0:-10] + ""    
            target_curr.execute(insert_sql_text)
            target_conn.commit()
            print insert_sql_text

            insert_sql_text = "insert into static_sdk_room_messge_send_x(xappkey,roomid,messge_send_cnt,pt,static_date) "

      insert_sql_text = insert_sql_text[0:-10] + ""    
      print insert_sql_text
      if (chg_num0%500 <> 0 ):
          target_curr.execute(insert_sql_text)
          target_conn.commit()

      print i
                    
    else:
        print "%s,no data !" % (yesterday)

src_curr.close()
src_conn.close()
target_curr.close()
target_conn.close()

-----------------------------
mysql2oracle.py

# -*- coding=utf-8 -*-
import cx_Oracle
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil

warnings.filterwarnings("ignore")

db_config_src = {
    'host': '172.16.96.34',
    'user': 'root',
    'passwd': '123',
    'port': 3306,
    'db': 'tableau_market'
}

def get_srcDB():
    try:
        src_conn = MySQLdb.connect(host=db_config_src['host'],user=db_config_src['user'],passwd=db_config_src['passwd'],port=db_config_src['port'])
        src_conn.autocommit(True)
        src_curr = src_conn.cursor()
        src_curr.execute("SET NAMES utf8");
        src_curr.execute("USE %s" % db_config_src['db']);
    
        return src_conn, src_curr
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

src_conn,src_curr = get_srcDB()


target_conn = cx_Oracle.connect('c##tv/c##tv@172.16.96.31:1521/orcl')
target_curr = target_conn.cursor()

'''
sql_text = "delete from static_sdk_room_messge_send_x where static_date='%s'" % (yesterday)
target_curr.execute(sql_text)
target_conn.commit()
'''

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 

select_sql_text = "select count(*) from static_sdk_room_messge_send where static_date='%s';" % (yesterday)
src_curr.execute(select_sql_text)
chg = src_curr.fetchall()
for chg_num in chg:
    chg_num0 = chg_num[0]
    print chg_num0

    if (chg_num0 >= 1):
      select_sql_text = "select xappkey,roomid,messge_send_cnt,pt,static_date from static_sdk_room_messge_send where static_date='%s';" % (yesterday)
      src_curr.execute(select_sql_text)

      delete_sql_text = "delete from static_sdk_room_messge_send_x where static_date='%s'" % (yesterday)
      target_curr.execute(delete_sql_text)
      target_conn.commit()

      i = 0
      insert_sql_text = "insert into static_sdk_room_messge_send_x(xappkey,roomid,messge_send_cnt,pt,static_date) "

      for item_room_message_send in src_curr.fetchall():
        xappkey = item_room_message_send[0]
        roomid = item_room_message_send[1]
        messge_send_cnt = item_room_message_send[2]
        pt = item_room_message_send[3]
        static_date = item_room_message_send[4]
    
        i += 1

        insert_sql_text = insert_sql_text + " select '%s','%s',%d,'%s','%s' from dual union all " % (xappkey,roomid,messge_send_cnt,pt,static_date)

        print i
        if (i%500 == 0):
            insert_sql_text = insert_sql_text[0:-10] + ""    
            target_curr.execute(insert_sql_text)
            target_conn.commit()
           #print insert_sql_text

            insert_sql_text = "insert into static_sdk_room_messge_send_x(xappkey,roomid,messge_send_cnt,pt,static_date) "

      insert_sql_text = insert_sql_text[0:-10] + ""    
      target_curr.execute(insert_sql_text)
      target_conn.commit()

      print i
                    
    else:
        print "%s,no data !" % (yesterday)

src_curr.close()
src_conn.close()
target_curr.close()
target_conn.close()

6、Tsv文本文件导入到Mysql
-----------------------------
tsv2mysql_batch_commit_1103.py

# -*- coding=utf-8 -*-
import smtplib
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil

warnings.filterwarnings("ignore")

db_config = {
    'host': '172.16.96.34',
    'user': 'root',
    'passwd': '123',
    'port': 3306,
    'db': 'tableau_market'
}

def getDB():
    try:
        conn = MySQLdb.connect(host=db_config['host'],user=db_config['user'],passwd=db_config['passwd'],port=db_config['port'])
        conn.autocommit(True)
        curr = conn.cursor()
        curr.execute("SET NAMES utf8");
        curr.execute("USE %s" % db_config['db']);
    
        return conn, curr
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

conn,curr = getDB()

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 

sql_text = "delete from static_sdk_room_messge_send where static_date='%s';" % (str(yesterday))
curr.execute(sql_text)

i = 0

sql_text = "insert into static_sdk_room_messge_send(xappkey,roomid,messge_send_cnt,pt,static_date) values "

for col_data in open("/root/pystudy/data/static_sdk_room_messge_send_%s.dat" % (yesterday)):
    xappkey         = col_data.split('\t')[0]
    roomid          = col_data.split('\t')[1]
    messge_send_cnt = int(col_data.split('\t')[2])
    pt              = col_data.split('\t')[3]
    static_date     = str(yesterday)

    i += 1

    sql_text = sql_text + "( '%s','%s',%d,'%s','%s')," % (xappkey,roomid,messge_send_cnt,pt,static_date)

    print i
    if (i%2000 == 0):
        sql_text = sql_text[0:-1] + ";"    
        curr.execute(sql_text)
#        print sql_text

        sql_text = "insert into static_sdk_room_messge_send(xappkey,roomid,messge_send_cnt,pt,static_date) values "

sql_text = sql_text[0:-1] + ";"    
print sql_text
curr.execute(sql_text)
print sql_text

print i

curr.close()
conn.close()

-----------------------------
tsv2mysql_batch_commit.py

# -*- coding=utf-8 -*-
import smtplib
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil

warnings.filterwarnings("ignore")

db_config = {
    'host': '172.16.96.34',
    'user': 'root',
    'passwd': '123',
    'port': 3306,
    'db': 'tableau_market'
}

def getDB():
    try:
        conn = MySQLdb.connect(host=db_config['host'],user=db_config['user'],passwd=db_config['passwd'],port=db_config['port'])
        conn.autocommit(True)
        curr = conn.cursor()
        curr.execute("SET NAMES utf8");
        curr.execute("USE %s" % db_config['db']);
    
        return conn, curr
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

conn,curr = getDB()

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 

sql_text = "delete from static_sdk_room_messge_send where static_date='%s';" % (yesterday)
curr.execute(sql_text)

i = 0

sql_text = "insert into static_sdk_room_messge_send(xappkey,roomid,messge_send_cnt,pt,static_date) values "

for col_data in open("/root/pystudy/data/static_sdk_room_messge_send_%s.dat" % (yesterday)):
    xappkey         = col_data.split('\t')[0]
    roomid          = col_data.split('\t')[1]
    messge_send_cnt = int(col_data.split('\t')[2])
    pt              = col_data.split('\t')[3]
    static_date     = col_data.split('\t')[4]

    i += 1

    sql_text = sql_text + "( '%s','%s',%d,'%s','%s')," % (xappkey,roomid,messge_send_cnt,pt,static_date)

    print i
    if (i%2000 == 0):
        sql_text = sql_text[0:-1] + ";"    
        curr.execute(sql_text)
#        print sql_text

        sql_text = "insert into static_sdk_room_messge_send(xappkey,roomid,messge_send_cnt,pt,static_date) values "

sql_text = sql_text[0:-1] + ";"    
curr.execute(sql_text)
#print sql_text

print i

curr.close()
conn.close()

-----------------------------
tsv2mysql.py

# -*- coding=utf-8 -*-
import smtplib
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil

warnings.filterwarnings("ignore")

db_config = {
    'host': '172.16.96.34',
    'user': 'root',
    'passwd': '123',
    'port': 3306,
    'db': 'tableau_market'
}

def getDB():
    try:
        conn = MySQLdb.connect(host=db_config['host'],user=db_config['user'],passwd=db_config['passwd'],port=db_config['port'])
        conn.autocommit(True)
        curr = conn.cursor()
        curr.execute("SET NAMES utf8");
        curr.execute("USE %s" % db_config['db']);
    
        return conn, curr
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

conn,curr = getDB()

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 

'''
sql_text = "delete from static_sdk_room_messge_send where static_date='%s';" % (yesterday)
curr.execute(sql_text)
'''


for col_data in open("/root/pystudy/data/static_sdk_room_messge_send_%s.dat" % (yesterday)):
    xappkey         = col_data.split('\t')[0]
    roomid          = col_data.split('\t')[1]
    messge_send_cnt = int(col_data.split('\t')[2])
    pt              = col_data.split('\t')[3]
    static_date     = col_data.split('\t')[4]

#os.remove("/root/pystudy/data/static_sdk_room_messge_send_%s.dat" % (yesterday))

    sql_text = "insert into static_sdk_room_messge_send(xappkey,roomid,messge_send_cnt,pt,static_date) select '%s','%s',%d,'%s','%s';" % (xappkey,roomid,messge_send_cnt,pt,static_date)
    curr.execute(sql_text)

curr.close()
conn.close()

7、Tsv文本文件导入到Oracle
-----------------------------
tsv2oracle_batch_commit.py

# -*- coding=utf-8 -*-
import cx_Oracle
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil

warnings.filterwarnings("ignore")

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 


ora_conn = cx_Oracle.connect('c##tv/c##tv@172.16.96.31:1521/orcl')
cursor = ora_conn.cursor()

sql_text = "SELECT max(roomid) from static_sdk_room_messge_send_x"
cursor.execute(sql_text)
row = cursor.fetchone()



sql_text = "delete from static_sdk_room_messge_send_x where static_date='%s'" % (yesterday)
cursor.execute(sql_text)
ora_conn.commit()

i = 0

sql_text = "insert into static_sdk_room_messge_send_x(xappkey,roomid,messge_send_cnt,pt,static_date) "

for col_data in open("/root/pystudy/data/static_sdk_room_messge_send_%s.dat" % (yesterday)):
    xappkey         = col_data.split('\t')[0]
    roomid          = col_data.split('\t')[1]
    messge_send_cnt = int(col_data.split('\t')[2])
    pt              = col_data.split('\t')[3]
    static_date     = col_data.split('\t')[4]

    i += 1

    sql_text = sql_text + " select '%s','%s',%d,'%s','%s' from dual union all " % (xappkey,roomid,messge_send_cnt,pt,static_date)
    print i
    if (i%1000 == 0):
        sql_text = sql_text[0:-10] + ""    
        cursor.execute(sql_text)
        #print sql_text
        ora_conn.commit()

        sql_text = "insert into static_sdk_room_messge_send_x(xappkey,roomid,messge_send_cnt,pt,static_date) "

sql_text = sql_text[0:-10] + ""    
#print sql_text
cursor.execute(sql_text)

ora_conn.commit()
cursor.close()
ora_conn.close()


-----------------------------
tsv2oracle.py

# -*- coding=utf-8 -*-
import cx_Oracle
import MySQLdb
import warnings
import datetime
import os
import os.path
import shutil

warnings.filterwarnings("ignore")

today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
tomorrow = today + datetime.timedelta(days=1)

print today
print yesterday 


ora_conn = cx_Oracle.connect('c##tv/c##tv@172.16.96.31:1521/orcl')
cursor = ora_conn.cursor ()

sql_text = "SELECT max(roomid) from static_sdk_room_messge_send_x"
cursor.execute(sql_text)
row = cursor.fetchone()



sql_text = "delete from static_sdk_room_messge_send_x where static_date='%s'" % (yesterday)
cursor.execute(sql_text)


for col_data in open("/root/pystudy/data/static_sdk_room_messge_send_%s.dat" % (yesterday)):
    xappkey         = col_data.split('\t')[0]
    roomid          = col_data.split('\t')[1]
    messge_send_cnt = int(col_data.split('\t')[2])
    pt              = col_data.split('\t')[3]
    static_date     = col_data.split('\t')[4]


    sql_text = "insert into static_sdk_room_messge_send_x(xappkey,roomid,messge_send_cnt,pt,static_date) select '%s','%s',%d,'%s','%s' from dual" % (xappkey,roomid,messge_send_cnt,pt,static_date)
    cursor.execute(sql_text)

ora_conn.commit()
cursor.close()
ora_conn.close()

BabyFish13
关注
专栏目录
python3.1连接mysql的组件.rar
03-13
Python 3.1 是一个广泛使用的编程语言，其在数据处理和数据库交互方面表现出色。在Python中连接MySQL数据库，通常我们会使用一个名为`pymysql`或`mysql-connector-python`的第三方库。不过，从提供的压缩包文件名...
python连接Oracle、Mysql数据库
tyler的博客
09-12 356
【代码】python连接Oracle、Mysql数据库
Python笔记06
Jlb520629的博客
07-14 4879
python操作数据库 1.python中的两个对象 1.连接对象 connection 作用：创建一个python程序到数据库的 连接通道 和jdbc连接是一个道理 方法： cursor 创建游标 commit 提交事务 rollback 事务回滚 参数： 1.mysql服务器地址 IP 2.端口号 port 3306 用户名 user root 密码 password root 数据库 database test 字符集 charset 2.游标对象 cursor 是操...
cursor.execute
weixin_42596011的博客
12-28 7703
cursor.execute 是 Python 中用于执行 SQL 语句的方法。它接受一个字符串参数，表示要执行的 SQL 语句，并返回一个整数，表示受影响的行数。例如： cursor.execute("SELECT * FROM users WHERE age > %s", (30,)) 这里，cursor 是通过数据库连接获取的数据库游标对象，execute 方法执行的是一条 SELE...
在APICloud中建立自己的X-APICloud-AppId和X-APICloud-AppKey
weixin_45277117的博客
10-05 1390
在APICloud中建立自己的X-APICloud-AppId和X-APICloud-AppKey（后面附上sha1的js文件下载） 这里使用的编码工具是apicloud官方的apicloud-studio2 看了官方的教程，当调用 APICloud 云开发接口时，比如使用api.ajax时在这个api里的参数里需要传用户注册头部信息：headers。里面包含了X-APICloud-AppId和...
开放api接口平台：appid、appkey、appsecret
西京刀客
11-22 6万+
appid、appkey、appsecret AppID：应用的唯一标识AppKey：公匙（相当于账号）AppSecret：私匙（相当于密码） token：令牌（过期失效） app_id 是用来标记你的开发者账号的, 是你的用户id, 这个id 在数据库添加检索, 方便快速查找。 2 app_key 和 app_secret 是一对出现的账号, 同一个 app_id 可以对应多个 app_ke...
向Oracle进行数据移植的方法.pdf
10-10
以下将详细解释如何从不同类型的数据库和文本文件向Oracle进行数据移植。 1. **文本文件数据移植到 Oracle** 当数据存储在文本文件中时，可以使用Oracle的数据加载工具（如ODL，Oracle Data Loader）进行移植。...
数据处理数据处理数据处理数据处理.doc
最新发布
05-09
在实际操作中，数据处理通常会借助一系列工具和技术来完成。以下是一些常用的数据处理工具和技术： - **Excel**：适用于简单的数据整理和初步分析。 - **数据库管理系统(DBMS)**：如MySQL、Oracle等，用于存储、...
Python实现异构数据库中表的记录数比对.pdf
06-29
因此，Python被广泛应用于系统编程、图像处理、数学处理、文本处理、数据库编程、网络编程、Web编程和多媒体应用等领域。 文章提到的数据比对实现涉及以下几个步骤： 1. 安装Python环境：首先需要访问Python官方...
etl.RAR_etl_etl oracle mysql_mysql的 etl函数_oracle_oracle向mysql e
09-24
ETL（Extract, Transform, Load）是数据仓库和大数据处理中的关键步骤，它涉及从不同源头提取数据，进行转换以适应目标系统需求，然后加载到目的地。在这个名为"etl.RAR"的压缩包中，我们可以看到作者提供了一个ETL...
python自动生成sql建表语句
03-19
python自动生成sql建表语句，可生成oracle，mysql，teradata建表语句，传入excel，生成建表语句
Python实现Oracle数据库同步
11-20
Python实现两个Oracle数据库同步工作
文本查重系统
04-01
整体框架部分参照牛客初级项目：python2.7+flaskweb+mysql，实现界面参照https://blog.csdn.net/wangyan2647/article/details/88430404
开放api接口平台都会有appid、appkey、appsecret
热门推荐
极客on之路
06-09 7万+
作者：肖旭 链接：https://www.zhihu.com/question/27814664/answer/140795440 来源：知乎 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。 我来回答你吧: app_id, app_key, app_secret , 对于平台来说, 需要给你的 你的开发者账号分配对应的权限: 1. app_id 是用来标记
APPKEY定义及APPKEY应用实例介绍
zhouting9907的博客
07-29 1万+
序 一个人有一个ID，所以便于区分和管理；那么试想，渠道统计，每个切入点都有一个固定的呢？ APPKEY定义 App key简称API接口验证序号，是用于验证API接入合法性的。接入哪个网站的API接口，就需要这个网站允许才能够接入，如果简单比喻的话：可以理解成是登陆网站的用户名。 拓展资料： API:应用程序接口(API:Application Program Interface)...
通过pymysql中cursor.execute()执行insert into 为mysql数据库插入数据时的坑及解决方法
XC_SunnyBoy的博客
09-29 2万+
mysql数据库中insert into 插入数据中的坑 正常来说 一般通过 insert into 表名(字段1, 字段2, ...) values (值1, 值2, ...) 就可以将数据插入到该表中，例如： 直接在mysql数据库中操作： insert into person(name,phone,hire_date,salary) values('张三', '13112345678', '2020-05-21', 15000.0); 通过远程连接数据库插入数据 connect = pymysql
execute(sql)三种执行方法的返回值表示的含义
suwu150
10-06 5万+
三种执行方法的返回值分别是什么含义:execute(sql)     (1). ResultSet executeQuery(String sql); 执行SQL查询，并返回ResultSet 对象。     (2). int executeUpdate(String sql); 可执行增，删，改，返回执行受到影响的行数。     (3). boolean execute(String s
cur.execute(sql,args)和cur.execute(sql)的区别
mjjyszazc的博客
04-29 2万+
python语法中操作数据库，执行一条sql语句有两种方式： 方式一： userid = “123” sql = “select id,name from user where id = ‘%s’” % userid cur.execute(sql) 方式二： userid = “123” sql = "“select id,name from user where id = %s” c...
分享一个自己写的用python比对数据库表数据的脚本
Brent的专栏
09-14 1万+
最近在做一个数据库异构复制的项目,客户表示需要一个数据比对的工具,我就自己写了一个异构数据库的比对python脚本.这个比对脚本只能比对数量,不能比对具体的记录.使用的sql语句也是最基础的select count(*) 这种,没有开并发所以对大表可能比对时间稍长. 基本原理是将需要比对的数据写到一张表里,先读取那个表里的数据,取出需要比对的表.然后创建多进程,同时在原端和目标端count.然后
Python数据处理框架quick_data_clean使用手册
手册详细介绍了框架的各种功能模块，帮助用户更高效地利用Python进行数据处理。 quick_data_clean的核心特点在于其易用性，它封装了多种数据处理功能，使得用户可以将注意力集中在数据处理逻辑上，而非语言本身的...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值