Écrire les Grands Pandas DataFrames de base de données SQL Server
J'ai 74 relativement grands Pandas DataFrames (Environ 34,600 lignes et 8 colonnes) que je suis en train de les insérer dans une base de données SQL Server aussi rapidement que possible. Après avoir fait quelques recherches, j'ai appris que le bon ole pandas.to_sql
fonction n'est pas bon pour ces grands insère dans une base de données SQL Server, qui a été la première approche que j'ai pris (très lente, presque une heure pour l'application de compléter vs environ 4 minutes lors de l'utilisation de base de données mysql.)
Cet article, et de nombreux autres StackOverflow postes ont été utile pour me pointer dans la bonne direction, cependant j'ai frappé un barrage routier:
Je suis en train d'utiliser SQLAlchemy de Base plutôt que de l'ORM pour les raisons expliquées dans le lien ci-dessus. Donc, je suis à la conversion de la dataframe à un dictionnaire, à l'aide de pandas.to_dict
et ensuite de faire un execute()
et insert()
:
self._session_factory.engine.execute(
TimeSeriesResultValues.__table__.insert(),
data)
# 'data' is a list of dictionaries.
Le problème est que l'insert n'est pas d'obtenir toutes les valeurs -- ils apparaissent comme un tas de vide parenthèse et j'obtiens cette erreur:
(pyodbc.IntegretyError) ('23000', "[23000] [FreeTDS][SQL Server]Cannot
insert the value NULL into the column...
Il y a des valeurs dans la liste des dictionnaires que j'ai passé, donc je ne peux pas comprendre pourquoi les valeurs ne s'affichent pas.
EDIT:
Voici l'exemple que je vais off de:
def test_sqlalchemy_core(n=100000):
init_sqlalchemy()
t0 = time.time()
engine.execute(
Customer.__table__.insert(),
[{"name": 'NAME ' + str(i)} for i in range(n)]
)
print("SQLAlchemy Core: Total time for " + str(n) +
" records " + str(time.time() - t0) + " secs")
- environ 4 minutes lors de l'utilisation de base de données mysql ...de sorte que le
to_sql()
est une solution viable, juste la connexion est plus lente en MSSQL par rapport à MySQL? Qui ODBC API que vous utilisez? Est serveur de base de données locale ou distante? Considérons une table temporaire d'importation et migrent ensuite vers la table finale. - À l'aide de
to_sql()
les rendements des performances acceptables avec MySQL, mais pas MSSQL. Je suis à l'aide de pyodbc. La base de données à distance, donc de l'écriture à des fichiers CSV et ensuite de faire un bulk insert via sql brut code ne sera pas vraiment de travail, soit dans cette situation. En outre, les utilisateurs doivent en vrac des privilèges d'administration pour le faire, ce qui n'est pas toujours possible pour les utilisateurs de cette application. - Envisager de contourner le pilote odbc et de l'utilisation strictement l'API Python - pmyssl Et MySQL ODBC API? pymysql? Même la structure des tables et des types de données dans les deux? Même nombre d'enregistrements? Vraiment enquêter sur ce point. Les deux sont de haut niveau de l'entreprise SGBDR et ne devrait pas effectuer que large (4 min vs ~60 minutes).
Vous devez vous connecter pour publier un commentaire.
J'ai une triste nouvelle pour vous, SQLAlchemy ne fait pas de mettre en œuvre des importations en bloc pour SQL Server, c'est en fait va juste faire de la même façon lente individuels des instructions INSERT
to_sql
est en train de faire. Je dirais que votre meilleur pari est d'essayer et de script quelque chose à l'aide de labcp
outil de ligne de commande. Voici un script que j'ai utilisé dans le passé, mais aucune garantie:Cet été récemment mis à jour SQLAchemy, version: 1.3.0 juste au cas où quelqu'un d'autre a besoin de savoir. Devriez faire de votre dataframe.to_sql déclaration beaucoup plus rapide.
https://docs.sqlalchemy.org/en/latest/changelog/migration_13.html#support-for-pyodbc-fast-executemany
moteur = create_engine(
"mssql+pyodbc://scott:tiger@mssql2017:1433/test?driver=ODBC+Chauffeur+13+pour+SQL+Serveur",
fast_executemany=True)