Re: [sqlalchemy] Copy a dataframe to postgresql database within a schema
Yes, you were right.
thanks.
El mié, 23 feb 2022 a la(s) 13:34, Mike Bayer (mike...@zzzcomputing.com)
escribió:
> the "from msilib import schema" import is at the top of your script. it
> looks like it's there by accident. are you using vscode? I find it often
> adds random package names as it guesses from my typing. I'd remove that
> line.
>
>
> On Wed, Feb 23, 2022, at 1:54 PM, Nahum Castro wrote:
>
> Hello All.
>
> I have a problem when I try to load a dataframe to postgresql and store it
> in a schema..
>
> from msilib import schema
> import pandas as pd
> import numpy as np
> import glob
> from sqlalchemy import create_engine
> from datetime import datetime, timedelta
>
> engine = create_engine('postgresql://nahum:inifedal@localhost:5432/dbwork'
> )
>
> archivos2=(glob.glob(
> "/Users/nahumcastro/Documents/mdt_work_files/records.csv"))
> df_telcel = pd.DataFrame(archivos2)
> for index, row in df_telcel.iterrows():
> datastore=pd.read_csv(str(row[0]), delimiter=',')
> datastore.to_sql('records', engine, schema='work')
>
> The problem that I have is that it asks for a library (msilib) available
> only on windows.
> https://docs.python.org/3/library/msilib.html
>
> If I do not use schema 'work' to store only in public schema there is no
> problem at all.
>
> /usr/local/bin/python3 /Users/nahumcastro/Documents/load.py
> Traceback (most recent call last):
> File "/Users/nahumcastro/Documents/load.py", line 1, in
> from msilib import schema
> ModuleNotFoundError: No module named 'msilib'
>
> I am on a macintosh computer.
> python 3.9.10
> pandas is 1.4.1
> sqlalchemy is 1.4.29
>
> Thanks in advance for any help.
> Nahum
>
> --
> *Nahum Castro González*
> Blvd. Perdigón 214, Brisas del Lago.
> CP 37207
> León, Guanajuato, México
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CABqArLi8j1uLmLZ1wBQFWEFoVp26KtLLfw0W8_oNc8mSRV0%2B1g%40mail.gmail.com
>
> .
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/1602204e-c1b3-4cf9-8428-6f7eb15d4d47%40www.fastmail.com
>
> .
>
--
*Nahum Castro González*
Blvd. Perdigón 214, Brisas del Lago.
CP 37207
León, Guanajuato, México
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CABqArLgqa_MVFcuzeZyGO2yAYyQzv8G98MRPmmtgH0zgSYFSAQ%40mail.gmail.com.
Re: [sqlalchemy] What is the best way to declare a table with 260 columns and add rows on that table
Hi Simon, Thank you for your help. I am brand new working with SQLalchemy, really appreciate if you explain how to generate the metadata with the list of column names from the .CSV to create the tables? On Wed, Feb 23, 2022, 3:52 PM Simon King wrote: > Build a list of Column objects from the columns in the CSV file, and > use that list to create a Table: > > https://docs.sqlalchemy.org/en/14/core/metadata.html > > Once you've created the Table, you can insert data into it using the > table.insert() method: > > > https://docs.sqlalchemy.org/en/14/core/tutorial.html#executing-multiple-statements > > Hope that helps, > > Simon > > On Wed, Feb 23, 2022 at 8:42 PM janio mendonca junior > wrote: > > > > Hi all, > > > > I have a inquiry from my job to create 2 tables related one-to-one and > insert some rows on the table. I have a .CSV with the data dictionary from > the table and I am wondering to know how to declare the tables columns > automatically without write one by one column (there are 260 columns). Same > thing for the insert, how to add rows to the multiple columns table without > write column by column? > > > > I have the data in a Data frame but I was not able to insert it using > df.to_sql from pandas. Do you guys have any similar example? > > > > Thank you all > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > > --- > > You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an email to sqlalchemy+unsubscr...@googlegroups.com. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CADF7wwb0_ncRuU_CadqFegE9583W-xWWD4x%3DGy8V%3DgW0jKtcyg%40mail.gmail.com > . > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAFHwexexvrpmr%3DEA4w%3DmncyiKyxj0yk%3Dcr9_%2Br%3Db3MCyOiHg%3DA%40mail.gmail.com > . > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CADF7wwYLD-OrxU9-Eq5mnSMoCBJ-EcFEcD%3DVXUEJJNLCx_dOjw%40mail.gmail.com.
Re: [sqlalchemy] What is the best way to declare a table with 260 columns and add rows on that table
Build a list of Column objects from the columns in the CSV file, and use that list to create a Table: https://docs.sqlalchemy.org/en/14/core/metadata.html Once you've created the Table, you can insert data into it using the table.insert() method: https://docs.sqlalchemy.org/en/14/core/tutorial.html#executing-multiple-statements Hope that helps, Simon On Wed, Feb 23, 2022 at 8:42 PM janio mendonca junior wrote: > > Hi all, > > I have a inquiry from my job to create 2 tables related one-to-one and insert > some rows on the table. I have a .CSV with the data dictionary from the table > and I am wondering to know how to declare the tables columns automatically > without write one by one column (there are 260 columns). Same thing for the > insert, how to add rows to the multiple columns table without write column by > column? > > I have the data in a Data frame but I was not able to insert it using > df.to_sql from pandas. Do you guys have any similar example? > > Thank you all > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CADF7wwb0_ncRuU_CadqFegE9583W-xWWD4x%3DGy8V%3DgW0jKtcyg%40mail.gmail.com. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexexvrpmr%3DEA4w%3DmncyiKyxj0yk%3Dcr9_%2Br%3Db3MCyOiHg%3DA%40mail.gmail.com.
[sqlalchemy] What is the best way to declare a table with 260 columns and add rows on that table
Hi all, I have a inquiry from my job to create 2 tables related one-to-one and insert some rows on the table. I have a .CSV with the data dictionary from the table and I am wondering to know how to declare the tables columns automatically without write one by one column (there are 260 columns). Same thing for the insert, how to add rows to the multiple columns table without write column by column? I have the data in a Data frame but I was not able to insert it using df.to_sql from pandas. Do you guys have any similar example? Thank you all -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CADF7wwb0_ncRuU_CadqFegE9583W-xWWD4x%3DGy8V%3DgW0jKtcyg%40mail.gmail.com.
Re: [sqlalchemy] Copy a dataframe to postgresql database within a schema
the "from msilib import schema" import is at the top of your script. it looks
like it's there by accident. are you using vscode? I find it often adds
random package names as it guesses from my typing. I'd remove that line.
On Wed, Feb 23, 2022, at 1:54 PM, Nahum Castro wrote:
> Hello All.
>
> I have a problem when I try to load a dataframe to postgresql and store it in
> a schema..
>
> from msilib import schema
> import pandas as pd
> import numpy as np
> import glob
> from sqlalchemy import create_engine
> from datetime import datetime, timedelta
>
> engine = create_engine('postgresql://nahum:inifedal@localhost:5432/dbwork')
>
> archivos2=(glob.glob("/Users/nahumcastro/Documents/mdt_work_files/records.csv"))
> df_telcel = pd.DataFrame(archivos2)
> for index, row in df_telcel.iterrows():
> datastore=pd.read_csv(str(row[0]), delimiter=',')
> datastore.to_sql('records', engine, schema='work')
>
> The problem that I have is that it asks for a library (msilib) available only
> on windows.
> https://docs.python.org/3/library/msilib.html
>
> If I do not use schema 'work' to store only in public schema there is no
> problem at all.
>
> /usr/local/bin/python3 /Users/nahumcastro/Documents/load.py
> Traceback (most recent call last):
> File "/Users/nahumcastro/Documents/load.py", line 1, in
> from msilib import schema
> ModuleNotFoundError: No module named 'msilib'
>
> I am on a macintosh computer.
> python 3.9.10
> pandas is 1.4.1
> sqlalchemy is 1.4.29
>
> Thanks in advance for any help.
> Nahum
>
> --
> *Nahum Castro González*
> Blvd. Perdigón 214, Brisas del Lago.
> CP 37207
> León, Guanajuato, México
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/CABqArLi8j1uLmLZ1wBQFWEFoVp26KtLLfw0W8_oNc8mSRV0%2B1g%40mail.gmail.com
>
> .
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/1602204e-c1b3-4cf9-8428-6f7eb15d4d47%40www.fastmail.com.
[sqlalchemy] Copy a dataframe to postgresql database within a schema
Hello All.
I have a problem when I try to load a dataframe to postgresql and store it
in a schema..
from msilib import schema
import pandas as pd
import numpy as np
import glob
from sqlalchemy import create_engine
from datetime import datetime, timedelta
engine = create_engine('postgresql://nahum:inifedal@localhost:5432/dbwork')
archivos2=(glob.glob(
"/Users/nahumcastro/Documents/mdt_work_files/records.csv"))
df_telcel = pd.DataFrame(archivos2)
for index, row in df_telcel.iterrows():
datastore=pd.read_csv(str(row[0]), delimiter=',')
datastore.to_sql('records', engine, schema='work')
The problem that I have is that it asks for a library (msilib) available
only on windows.
https://docs.python.org/3/library/msilib.html
If I do not use schema 'work' to store only in public schema there is no
problem at all.
/usr/local/bin/python3 /Users/nahumcastro/Documents/load.py
Traceback (most recent call last):
File "/Users/nahumcastro/Documents/load.py", line 1, in
from msilib import schema
ModuleNotFoundError: No module named 'msilib'
I am on a macintosh computer.
python 3.9.10
pandas is 1.4.1
sqlalchemy is 1.4.29
Thanks in advance for any help.
Nahum
--
*Nahum Castro González*
Blvd. Perdigón 214, Brisas del Lago.
CP 37207
León, Guanajuato, México
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CABqArLi8j1uLmLZ1wBQFWEFoVp26KtLLfw0W8_oNc8mSRV0%2B1g%40mail.gmail.com.
[sqlalchemy] Issue "translating" raw SQL to SQLAlchemy ORM query
*FROM*: https://stackoverflow.com/questions/71225408/issue-translating-raw-sql-to-sqlalchemy-orm-query I have the following raw SQL statement that I am having trouble "translating" into a SQLAlchemy query: (the hardcoded value 38 is just for testing) *SELECT * FROM public.data_appquestion AS question * */* ANSWER JOIN */ * *LEFT JOIN (SELECT * FROM public.data_appanswer) AS answer * *ON ( answer.separation_app_question_id = question.id AND answer.is_active = true * *AND answer.separation_app_session_id = 38 ) * */* OPTION XREFS JOIN */ * *LEFT JOIN (SELECT * FROM public.data_appansweroptionxref) AS options_xref * *ON ( options_xref.separation_app_answer_id = answer.id ) * */* OPTIONs JOIN */ * *LEFT JOIN (SELECT * FROM public.data_appoption) AS answered_option * *ON ( options_xref.separation_app_option_id = answered_option.id ) * */* UPLOAD JOIN */ * *LEFT JOIN (SELECT * FROM public.data_appfileupload) AS uploads * *ON ( uploads.separation_app_answer_id = answer.id ) * *WHERE question.is_active = true * *AND answer.is_active = true OR answer.is_active = NULL * *AND options_xref.is_active = true OR options_xref.is_active = NULL * *AND uploads.to_delete = false OR uploads.to_delete = NULL * *ORDER BY question.id;* I have tried something like this, but the "filter" statement already does not seem to work as I need it to: *db_questions = db.query(models.AppQuestion).\* *filter(models.AppQuestion.is_active == True).\* *outerjoin(models.AppAnswer, and_( models.AppAnswer.app_question_id == models.AppQuestion.id, models.AppAnswer.app_session_id == 38 ) ).\* *outerjoin(models.AppAnswer.app_options).\* *outerjoin(models.AppAnswerOptionXref.app_option).\* *outerjoin(models.AppFileUpload.app_question).\* *order_by(asc(models.AppQuestion.order_number)).all() * The models all have the relevant "relationship" entries, so there is no issue for the query to find the relevant models via their foreign keys. The issue is that they are not filtered as they are in the raw SQL. My result includes a joined "AppAnswer", but it does not filter it according to ```app_session_id == 38 I'm not very familiar with joining SQL queries and usually working with the Django ORM, which never had me run into an issue like this. Let me know if I need to add more info and thanks a lot for any replies! -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/3b1eb2d6-1736-41f3-9cd3-29f0cd9af737n%40googlegroups.com.
