Conexión con bases de datos tipo SQL

5/28

Lectura

Cómo usar Pandas y Python para conectar con tu base de datos SQL

Pandas cuenta con una funcionalidad que facilita el acceso a tus bases de datos tipo SQL, para ello te mostrare algunos ejemplos:

  • PostgreSQL:

Valida que tengas la librería psycopg2 usando el comando import. Si no está instalada en tu ambiente, usa el comando !pip install psycopg2 en la terminal de python para instalarlo.

Comenzamos cargando las librerías:

import pandas as pd
import psycopg2

Luego creamos el elemento de conexión con el siguieente código:

conn_sql = psycopg2.connect(user = "user_name",
                            password = "password",
                            host = "xxx.xxx.xxx.xxx",
                            port = "5432",
                            database = "postgres_db_name")

Seguido simplemente definimos nuestra query en SQL:

query_sql = '''
select *
from table_name
limit 10
'''

Y creamos nuestro dataframe:

df = pd.read_sql(query_sql, sql_conn)
df.head(5)
  • SQL Server:

Valida que tengas la librería pyodbc usando el comando import, si no está instalada en tu ambiente, usa el comando !pip install pyodbc en la terminal python para instalarlo.

Comenzamos cargando las librerías:

import pandas as pd
import pyodbc

Luego creamos el elemento de conexión con el siguiente código:

driver = '{SQL Server}'
server_name = 'server_name'
db_name = 'database_name'
user = 'user'
password = 'password'
sql_conn = pyodbc.connect('''
DRIVER={};SERVER={};DATABASE={};UID={};PWD={};
Trusted_Connection=yes
'''.format(driver, server_name, db_name, user, password))

O si tienes el DSN:

dsn = 'odbc_datasource_name'
sql_conn = pyodbc.connect('''
DSN={};UID={};PWD={};Trusted_Connection=yes;
'''.format(dsn, user, password))
Seguido simplemente definimos nuestra query en SQL:	
query_sql = 'select * from table_name limit 10'

Y creamos nuestro dataframe con:

df = pd.read_sql(query_sql, sql_conn)
df.head(5)
  • MySQL / Oracle / Otras:

Valida que tengas la librería sqlalchemy usando el comando import, si no está instalada en tu ambiente, usa el comando !pip install sqlalchemy en la terminal de python para instalarlo.

Comenzamos cargando las librerías:

import pandas as pd
import sqlalchemy as sql
Escogemos nuestra base de datos, Oracle, MySql o la de tu preferencia:
database_type = 'mysql'
database_type = 'oracle'

Luego creamos el elemento de conexión con el siguiente código:

user = 'user_name'
password = 'password'
host = 'xxx.xxx.xxx.xxx:port'
database = 'database_name'

conn_string = '{}://{}:{}@{}/{}'.format(
database_type, user, password, host, database)

sql_conn = sql.create_engine(conn_string)

Seguido simplemente definimos nuestra query en SQL:

query_sql = '''
select *
from table_name
limit 10
'''

Y creamos nuestro dataframe con:

df = pd.read_sql(query_sql, sql_conn)
df.head(5)

La libreria sqlalchemy también soporta PostgreSQL y otras fuentes de datos.

Aportes 56

Preguntas 17

Ordenar por:

Los aportes, preguntas y respuestas son vitales para aprender en comunidad. Regístrate o inicia sesión para participar.

Impresionante, ojalá este curso estuviera más al inicio en la ruta de Data Science, muy bien explicado todo, 10/10

Me gustaria una clase que podamos usar esto e integrarlo con las bases de datos.

Otra forma de realizar la conexion: (en mi caso una BBDD MySQL alojada remotamente en un servidor en la nube)

!pip install pymysql
import pymysql
conn = pymysql.connect(host=‘YOUR_IP_HOST’, port=DB_PORT, user=‘YOUR_USER’, passwd=‘YOUR_PASSWD’, db=‘YOUR_DB_NAME’)

Luego:
cursor = conn.cursor()
query = (“select * from YOUR_TABLE_NAME”)
cursor.execute(query)

Espero que sea de utilidad este dato.

Ahora estoy buscando la manera de conectarme a mi base de datos local desde Google Colab pero aun no lo logro, si alguno conoce la manera de realizar la conexión le agradezco el dato.

Clases como esta, deben hacer el video, aunque sea con alguno de los ejemplos. Una cagada

para los que no logren conectar con postgresql ejecuten lo siguiente

!sudo apt-get -y -qq update
!sudo apt-get -y -qq install postgresql
!sudo service postgresql start
!sudo -u postgres psql -U postgres -c "ALTER USER postgres PASSWORD 'postgres'"
conn_sql = psycopg2.connect(user = "postgres",
                            password = "postgres",
                            host = "127.0.0.1",
                            port = "5432",
                            dbname = "postgres")

con eso no deberían de tener ningún problema 😃

Comando para saber la ip

!curl ipecho.net/plain

Hola buena tarde, no lo gro conectarme a Postgres
OperationalError: could not connect to server: Connection refused
Is the server running on host “127.0.0.1” and accepting
TCP/IP connections on port 5432?
pensaba que era la IP pero pongo la privada y me sale el mismo mensaje, ojalá puedan ayudarme, gracias.

df = pd.read_sql(query_sql, sql_conn)

Creo que aqui hay un error, siguiendo la logica seria

df = pd.read_sql(query_sql, conn_sql)```

Como se puede conectar desde Google Colab a SQL Server?, al realizar los pasos indicados no me funciona

tengo este error al conectar con mysql; ModuleNotFoundError: No module named ‘MySQLdb’

<h3>PostgreSQL and Collaboratory</h3>
  1. Install psycopg2-binary and postgresql utils
!pip install psycopg2-binary
!apt install postgresql postgresql-contrib &>log
  1. Imports
import pandas as pd
import psycopg2
  1. Start postgresql service
!service postgresql start

* Starting PostgreSQL 10 database server
   ...done.
  1. Add user root like SUPERUSER
!sudo -u postgres psql -c "CREATE USER root WITH SUPERUSER"

* CREATE ROLE
  1. Load sql extension
%load_ext sql

* The sql extension is already loaded. To reload it, use:
  %reload_ext sql
  1. Config sql magic
%config SqlMagic.feedback=False 
%config SqlMagic.autopandas=True
  1. SQL PostgreSQL connection
%sql postgresql+psycopg2://@/postgres

*  Connected: @postgres
  1. Create query:
query_sql = ''' select * from pg_catalog.pg_tables limit 10 '''
  1. Create DataFrame
df = %sql $query_sql


* postgresql+psycopg2://@/postgres
  1. Load head of df
df.head(10)

Les dejo mi ejemplo de la conexión con mysql

import sqlalchemy as sql
import pandas as pd

database_type = 'mysql'
user = 'admin'
password = 'password'
host = '127.0.0.1:3306'
database = 'foodrestaurants'

conn_string = '{}://{}:{}@{}/{}'.format(
    database_type, user, password, host, database)

print(conn_string)
sql_conn = sql.create_engine(conn_string)

query_sql = '''
select *
from datafood
limit 100
'''

df = pd.read_sql(query_sql, sql_conn)
print(df.head(5))

Si sale el siguiente error:
ModuleNotFoundError: No module named ‘MySQLdb’

Con solo instalar mysqlclient.

Yo lo hice mediante PyCharm -> File -> Settings -> Project -> Python Interpreter -> + -> y buscamos el paquete.

Sale error al conectar con PostgreSQL:
OperationalError: connection to server at “localhost” (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
Supongo que en “host” es localhost o 127.0.0.1 en caso de que la BBDD esté en local verdad?

Otra forma para conectarnos a mysql es a través de mysql-connector. Para instalarlo ejecutamos:

!pip install mysql-connector-python

Posterior a la instalación lo importamos con:

import mysql.connector as sql

Procedemos a crear la conexión con la base de datos:

db_connection = sql.connect(
host='localhost',
database='supermarket', 
user='root', 
password='1234')

El puerto por defecto es el 3306 pero puedes especificarlo con el parametro port = '3306’
Cabe aclarar que los datos son solo de ejemplo (¿Quién iba a tener una contraseña tan fácil?).

Ahora solo nos queda crear el DataFrame como se hace con las demás bases de datos:

query = 'SELECT * FROM products'
df = pd.read_sql(query, con=db_connection)

😄 por el reto que pusieron en platzi en estos días me pasé para este curso directo desde matemáticas para datascience, aún no lleve sql XD.
Aiudaa

No logré hacer la conexión a MS-SQL desde colab, no sé si es porque se está utilizando una conexión odbc y estoy en mac?

Buenas, yo he usado lo expuesto en esta clase para conectarme desde un notebook a una instacia de postgreSQL alojada en AWS RDS. no comparto código por motivos de privacidad, pero si tienen alguna duda les puedo ayudar.

Excelente poder usar PostgreSQL 😃

para conectar a microsoft sql se tienen que instalar el “sql driver” from microsoft.

# install sql driver  https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15
# apt-utils
RUN su && \
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
apt-get update && \
ACCEPT_EULA=Y apt-get install -y msodbcsql17 && \
ACCEPT_EULA=Y apt-get install -y mssql-tools

SHELL [ "/bin/bash", "-c" ]
RUN source ~/.bashrc && \
apt-get install -y unixodbc-dev

Para los de MySQL si les sale el error:

ModuleNotFoundError: No module named 'MySQLdb'```

me sirvió



from sqlalchemy import create_engine

import pymysql
pymysql.install_as_MySQLdb()```

Gracias por el resumen

Excelente e importante

Creando una tabla nueva en la base de datos en Postgres desde Python

#Cursor
cursor = conn_sql.cursor()
#statement para creación de la tabla
crear_tabla = "create table nueva_tabla" "(id bigint, titulo varchar(128), resumen varchar(256), historia text);"
#creación de la tabla en POSTGRESQL
cursor.execute(crear_tabla)
conn_sql.commit()

Gracias…!!

Realice la conexión exitosa a la Base de Datos PotgreSQL, desde Visual Studio Code.

Sería genial que hicieran un curso de SQLAlchemy existe poca documentación al respecto actualmente y la que esta no es tan amigable como lo es un curso de platzi.

Me gusta mucho trabajar con PostgreSQL lo he trabajado con el framework de Groovy on Grails

a mi no me conectaba a las base de datos remotas, tube que instalar mysqlclient

pip install mysqlclient
pip install sqlalchemy

import pandas as pd
import sqlalchemy as sql

Para los que estan instalando MySql y les apareció el siguiente error:

No module named MySQLdb !!!

Deben instalar mysqlclient:

!pip install sqlalchemy
!pip install mysqlclient

Ahora voy entendiendo la importancia de bases de datos. Bueno, ya sé que sigue!
👾

Hasta el momento no sé SQL o NoSQL, está en lista, sin embargo he tomado apuntes para poder practicar apenas sepa.
Si alguno de ustedes tiene algún consejo o material de practica, si gustan pueden compartirlo.

Aquí tienen otra libreria para postgre:

decir que me funcionó mucho mejor está de pg8000 que la de pgycopg2 en conexiones a instancias Aurora RDS de AWS.

Saludos.

No pude establecer la conexión con MySQL. Siempre da un error de conexión.
Estoy intentando conectar la database platziblog que se creó en el curso de fundamentos de bases de datos. Si a alguno le funciona le agradezco por favor compartir el código.
Gracias

Al parecer sqlalchemy funciona correctamente con Python 2.x

Para Python 3.x es mejor usar: pymysql

A continuación muestro un ejemplo para conectar con la base “platziblog” que se construyó en el curso de “Fundamentos de Bases de Datos”:

# cargamos  pandas:
import pandas as pd

# cargamos la libreria para conectar bases de datos tipo 'SQL':
import pymysql

# Creamos la conexión con la base de datos :
conn = pymysql.connect(
    host='localhost',
    port=3306, 
    user='root', 
    passwd='', 
    db='platziblog' # <- nombre de la base de datos
    )

cursor = conn.cursor()

# Hacemos una consulta de la tabla "categorias":
query = ('SELECT * FROM categorias')
cursor.execute(query)

# Pasamos la consulta de MySQL a un dataframe:
df = pd.read_sql(query, conn)
df.head()

Excelente

Les recomiendo echar un vistazo a Deepnote y su documentación. Aquí es un poco más sencillo hacer integraciones.

Hola!
Les comparto la manera en que probé el funcionamiento.
Es importante que, pese a que sea una prueba, entendamos que la información de conexión a base de datos es muy sensible y no debe exponerse de esa manera en el código de la aplicación.
Para resolverlo, me apoye de pandas para leer un archivo .csv externo en donde alojé los valores para generar la cadena.

Comparto el código y en el, las notas que consideré relevantes para la ejecución de mi programa.

Espero sea útil, saludos!

from icecream import ic
import sqlalchemy as sql
import pandas as pd

"""
Se define una constante con el archivo de configuraciones para generar la conexion.
En este caso, se hace uso de pandas para que dicho archivo sea un csv

"""
CONFIG = './files/database_conf.csv'

def connect():

    """
    Se lee el archivo de configuraciones y se extraen los valores, mismos
    con los que se genera la cadena de conexión que se le pasará a sqlalchemy

    """
    df = pd.read_csv(CONFIG)
    database_type = df.iloc[5, 1]
    user = df.iloc[0, 1]
    password = df.iloc[1, 1]
    host = df.iloc[2, 1]
    database = df.iloc[3, 1]
    port = df.iloc[4, 1]

    conn_string = '{}://{}:{}@{}:{}/{}'.format(database_type, user, password, host, port, database)

    """
    El origen de datos es una base en postgres. Pese a que se usa la 
    libreria de sqlalchemy, manda un error debido a que no encuentra un driver
    valido de conexión, por lo que es necesario instalar psycopg2
    Esta libreria contiene los drivers de conexion, por lo que, si se 
    requiere, en este caso no es necesario instalar sqlalchemy, basta
    con psycopg2 y en tal caso, usar la plantilla de conexión que nos
    proporciono el profesor en el documento de clase

    """
    conn = sql.create_engine(conn_string)
    query_sql = 'SELECT * FROM plan_ocb po ORDER BY id'
    query_result = pd.read_sql(query_sql, conn)
    
    ic(query_result)
    
    dir_pandas = './files/{}'.format('select_from_postgresql.csv')
    query_result.to_csv(dir_pandas, index = False)


if __name__ == '__main__':
    connect()

pueden instalar psycopg2 usando anaconda.

Excelente informacion

interesante

Alguien me podría ayudar en un ejemplo de como conectarme a una BD ElasticSearch, mil gracias si pueden ayudarme

Que buena información!

Excelente información, muchas gracias

Para poderlo conectar a mi db de Postgresql tuve que conectar mi notebook a un local runtime de Jupyter y ahí me funcionó, sin embargo, no creo que haya sido por el código sino por el firewall. Postgresql hay que confirgurarlo para que reciba conexiones remotas y hay que habilitar el puerto. A mí no me funcionó pero creo que es el camino jaja, sin embargo, con el local runtime van a la fija

Pero que cosa tan maravillosa es Pandas! Justo lo que estaba necesitando para conectarme a una base de datos que estaba desarrollando en local. Más adelante veremos si podemos insertar datos nuevos en la Base de Datos?

No realicé la prueba desde colab, sino desde jupyter notebook de anaconda para conectar con postgresql y funcionó sin necesidad de pasos adicionales

Impresionante, cada clase mas enamorado de pandas

Esto es fabuloso!!!

Me arroja el mismo error que al compañero ;
ModuleNotFoundError: No module named ‘MySQLdb’

Ya recuerdo, lo que no explica, es que los códigos mencionados sirven para hacer tests en un mismo ordenador, es decir tu gestor de base de datos y tu código en un ide también en el mismo ordenador. Lo que no dice tampoco es que lo importante de estas conexiones es que trabaje de manera remota y estas configuraciones no sirven, es decir la abse de datos en un ordenador en un lugar X, y el código o la api en otro ordenador Y.

! pip install flask-ngrok
import werkzeug
werkzeug.cached_property = werkzeug.utils.cached_property
from flask_ngrok import run_with_ngrok
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_restplus import Api, Resource, fields
app = Flask(name)
app.config[‘SQLALCHEMY_DATABASE_URI’] = ‘postgresql://postgres:[email protected]/predicciones’ #, pool_pre_ping=True’
api = Api(app)
run_with_ngrok(app)

Aunque ese enfoque es para una API, lo importante es qusa ngrok y werkzeug. No lo he adptado a armar un df.

Así, lo que enseña sobre este tema es incompleto y hasta obsoleto por su enfoque.

Perfecto, espero darle utilidad con PostgreSQL

Que buena informacion!!

Hola, alguien sabe como puedo conseguir el user y el host de mi servidor local con MySQL para así conectar la base de datos? Gracias!

Muy útil, de igual forma es muy interesante utilizar sql alchemy para escribir en bases de datos

Excelente gracias.

Para quien tenga problemas instalando pyodbc en Colab, a mi me funcionó de la siguiente manera:

!apt install unixodbc-dev
!pip install pyodbc