How to consult data in MySQL and be able to save in SQL Server? (Python)

Question

How to consult data in MySQL and be able to save in SQL Server? (Python)

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Good morning I have a doubt, I have a code generated in Python which what it does is to consult a MySQL table named INFORMATION_SCHEMA.PROCESSLIST in the following way:

SELECT *, IF(time >= 10,"Bad","Good") AS Estado FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id <> connection_id();

Once this fulfills my conditions by means of python code, I tell it to save those processes and also kill them with the KILL command, but the data I send to save is in a table that creates in MySQL .

Now what I want is to continue consulting MySQL, but I want to know if there is a way to save the data queried in a table but SQL Server, basically consult MySQL and save in SQL Server.

Annex the code:

#! / usr / bin / python
# - * - coding: UTF-8 - * -
import os, sys
import signal, os, sys
import MySQLdb
conexion_mysql = MySQLdb.connect(host = '10.248.204.43', user = 'root', passwd = 'sahc', db = 'Biblioteca')

cur = conexion_mysql.cursor()
consulta = ('SELECT *, IF(time >= 10,"Bad","Good") AS Estado FROM    INFORMATION_SCHEMA.PROCESSLIST WHERE id <> connection_id()')
cur.execute(consulta)
for row in cur.fetchall():
    resultado = row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8]
    sql = "INSERT INTO prueba VALUES (" + str(row[0]) + "," + "'" + (row[1] or '') + "'" + "," + "'" + (row[2] or '') + "'" + "," + "'" + (row[3] or '') + "'" + "," + "'" + (row[4] or '') + "'" + "," + str(row[5]) + "," + "'" + (row[6] or '') + "'" + "," + "'" + (row[7] or '') + "'" + "," + "now()" + ")"
    cur.execute(sql)
    conexion_mysql.commit()
    print "Datos Guardados Exitosamente"
    if row[0] == row[0]:
        matar = "KILL " + str(row[0])
        cur.execute(matar)
        conexion_mysql.commit()
        print "Datos eliminados"

As you can see, I keep in MySQL, I want to continue consulting in MySQL but save the data in SQL Server.

I would appreciate the help:)

mysql python python-3.x sql-server

asked by Santiago Huh 08.06.2017 в 17:11

source

2 answers

1

I do not see big problems, what you have to do is build a second connection to SQL Server, obviously you will have to use another package to connect to it, I usually use pypyodbc , but there are several more. I attached a simple example:

import pypyodbc

# Completar <<server>> <<user>> y <<passw>>
conn = pypyodbc.connect("DRIVER={SQL Server};SERVER=<<server>>;DATABASE=master;UID=<<user>>;PWD=<<passw>>")
cur = conn.cursor()

# Este es tu INSERT revisar compatibilidad MySql / MSSQL
sql = "INSERT INTO prueba VALUES (" + str(row[0]) + "," + "'" + (row[1] or '') + "'" + "," + "'" + (row[2] or '') + "'" + "," + "'" + (row[3] or '') + "'" + "," + "'" + (row[4] or '') + "'" + "," + str(row[5]) + "," + "'" + (row[6] or '') + "'" + "," + "'" + (row[7] or '') + "'" + "," + "now()" + ")"
cur.execute(sql)
cur.commit()

# Cierro conexión
conn.close()

answered by 08.06.2017 в 17:44

Display Firebase data in a table in HTML Can I pass a shortcode as an attribute of another shortcode? Wordpress

score 0 · Accepted Answer

The advice of @Patricio Morocho served me but I used another import leaving the final code like this:

#! / usr / bin / python
# - * - coding: UTF-8 - * -
import os, sys
import signal, os, sys
import MySQLdb #MySQL
import pymssql #SQL server 2016

#Conexión a MySQL
conexion_mysql = MySQLdb.connect(host = '10.248.204.43', user = 'root', passwd = 'sahc', db = 'Biblioteca')

#Conexión a SQL Server
server = "AXPOS10"
user = "santiago"
password = "Python@Monitor"
base = "StoragePython"
conexion_sql = pymssql.connect(server, user, password, base)

cur = conexion_mysql.cursor() #Cursor de Mysql
cur2 = conexion_sql.cursor() #Cursor de SQL Server

#Consulta a MySQL
consulta = ('SELECT *, IF(time >= 10,"Bad","Good") AS Estado FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id <> connection_id()')
cur.execute(consulta) #Ejecución de consulta MySQL apartir del for es ejecución a SQL Server y en el if se ejecuta el comando KILL en MySQL
for row in cur.fetchall():
    resultado = row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8]
    sql = "INSERT INTO LogKillProcesstMySQL VALUES (" + str(row[0]) + "," + "'" + (row[1] or '') + "'" + "," + "'" + (row[2] or '') + "'" + "," + "'" + (row[3] or '') + "'" + "," + "'" + (row[4] or '') + "'" + "," + str(row[5]) + "," + "'" + (row[6] or '') + "'" + "," + "'" + (row[7] or '') + "'" + "," + "GETDATE()" + ")"
    cur2.execute(sql) #Ejecución de insertado SQL Server
    conexion_sql.commit()
    print "Datos Guardados Exitosamente"
    if row[0] == row[0]:
        matar = "KILL " + str(row[0])
        cur.execute(matar)
        conexion_mysql.commit()
        print "Datos eliminados"

The result was successful!