Db

gistfile1.txt

import pyodbc
import MySQLdb
import datetime


## Select the last event in the MySQL # DEBUG:
connection = ""
mysqldb = MySQLdb.connect(db='blindaje_sistema', user='root', passwd='')
mysqldb = mysqldb.cursor()

mysqldb.execute("SELECT MAX(ID) FROM acc_monitor_log")

for row in mysqldb.fetchall():
        lastid = row[0]
mysqldb.close()


# Specifying the ODBC driver, server name, database, etc. directly

cnxn = pyodbc.connect('DRIVER={/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2};SERVER=192.168.129.25;DATABASE=ZKAccess;UID=root;PWD=root')
# Create a cursor from the connection
cursor = cnxn.cursor()

cursor.execute("""SELECT TOP 100
id, status, log_tag, [time], pin, device_id, device_sn, device_name, state, event_type, description, event_point_type, event_point_id, event_point_name,
( SELECT TOP (1) [card_no] FROM [ZKAccess].[dbo].[acc_monitor_log] m2 where m2.id <= m1.id and m2.device_id = m1.device_id and (m2.card_no != 0 and m2.card_no != '') order by id desc) as tarjetaNueva
FROM [ZKAccess].[dbo].[acc_monitor_log] as m1 where event_type = 28 or event_type = 102 and id > ? order by id desc""", (lastid))
insertrows = []


for rowsql in cursor.fetchall():
    insertrows.append(rowsql)


mysqldb = MySQLdb.connect(db='blindaje_sistema', user='root', passwd='')
dbcursor = mysqldb.cursor()
sql_statement = "INSERT INTO acc_monitor_log (id, status, log_tag, time, pin,device_id, device_sn, device_name, state, event_type, description, event_point_type, event_point_id, event_point_name, card_no) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
dbcursor.executemany(sql_statement,insertrows)
mysqldb.commit()
mysqldb.close()