Запись в базу данных MySQL с помощью панд с использованием SQLAlchemy, to_sql

Question

Пытаюсь записать pandas dataframe в таблицу MySQL, используя to_sql. Ранее использовался flav ='mysql', однако в будущем он будет устаревшим и хотел начать переход на использование движка SQLAlchemy.

образец кода:

import pandas as pd
import mysql.connector
from sqlalchemy import create_engine

engine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False)
cnx = engine.raw_connection()
data = pd.read_sql('SELECT * FROM sample_table', cnx)
data.to_sql(name='sample_table2', con=cnx, if_exists = 'append', index=False)

Чтение работает нормально, но у to_sql есть ошибка:

DatabaseError: Ошибка при выполнении sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': неверное количество аргументов при форматировании строки

Почему похоже, что он пытается использовать sqlite? Как правильно использовать соединение sqlalchemy с mysql и, в частности, с mysql.connector?

Я также попытался передать движок в качестве соединения, и это дало мне ошибку, ссылающуюся на объект курсора.

data.to_sql(name='sample_table2', con=engine, if_exists = 'append', index=False)
>>AttributeError: 'Engine' object has no attribute 'cursor'

Answer 1

Использование движка вместо raw_connection() сработало:

import pandas as pd
import mysql.connector
from sqlalchemy import create_engine

engine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False)
data.to_sql(name='sample_table2', con=engine, if_exists = 'append', index=False)

не ясно, почему, когда я попробовал это вчера, это дало мне более раннюю ошибку

Answer 2

В качестве альтернативы используйте pymysql пакет...

import pymysql
from sqlalchemy import create_engine
cnx = create_engine('mysql+pymysql://[user]:[pass]@[host]:[port]/[schema]', echo=False)

data = pd.read_sql('SELECT * FROM sample_table', cnx)
data.to_sql(name='sample_table2', con=cnx, if_exists = 'append', index=False)

Answer 3

Используя pymysql и sqlalchemy, это работает для Pandas v0.22:

import pandas as pd
import pymysql
from sqlalchemy import create_engine

user = 'yourUserName'
passw = 'password'
host =  'hostName'  # either localhost or ip e.g. '172.17.0.2' or hostname address 
port = 3306 
database = 'dataBaseName'

mydb = create_engine('mysql+pymysql://' + user + ':' + passw + '@' + host + ':' + str(port) + '/' + database , echo=False)

directory = r'directoryLocation'  # path of csv file
csvFileName = 'something.csv'

df = pd.read_csv(os.path.join(directory, csvFileName ))

df.to_sql(name=csvFileName[:-4], con=mydb, if_exists = 'replace', index=False)

"""
if_exists: {'fail', 'replace', 'append'}, default 'fail'
     fail: If table exists, do nothing.
     replace: If table exists, drop it, recreate it, and insert data.
     append: If table exists, insert data. Create if does not exist.
"""

Answer 4

Я знаю, что в заголовок вопроса включено слово SQLAlchemy, однако я вижу в вопросах и ответах необходимость импортировать pymysql или mysql.connector, а также возможно выполнить работу с pymysql, не вызывая SQLAlchemy.

import pymysql
user = 'root'
passw = 'my-secret-pw-for-mysql-12ud' # In previous posts variable "pass"
host =  '172.17.0.2'
port = 3306

database = 'sample_table' # In previous posts similar to "schema"

conn = pymysql.connect(host=host,
                       port=port,
                       user=user, 
                       passwd=passw,  
                       db=database)

data.to_sql(name=database, con=conn, if_exists = 'append', index=False, flavor = 'mysql')

Я думаю, что это решение может быть хорошим, хотя оно не использует SQLAlchemy.

Answer 5

Быстрое решение проблемы включает следующую строку в вашем скрипте:

pd.io.sql._SQLALCHEMY_INSTALLED = True

Причина в том, что to_sql звонки pandasSQL_builder который сам называет _is_sqlalchemy_connectable, который проверяет, установлен ли sqlalchemy. По какой-то причине, однако, даже с установленной sqlalchemy, функция, кажется, думает, что это не так. Я пользуюсь пандами 0.24.2.

Answer 6

У меня есть @ в поле пароля, и это создает проблему. Как мы можем это исправить. engine = create_engine("mysql+pymysql://dbuser:DBuser @ 12345 @ xyz/table") Здесь DBuser @ 12345 - пароль. но как-то скрипт не может правильно его интерпретировать. Это пользователь 12345 @ xyz в качестве имени хоста вместо xyz