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 explanation.
I managed to create the table by using df.to_sql. My mistake was because I
was using a serie to sql instead of a dataframe.
On Fri, Feb 25, 2022 at 5:33 AM Simon King wrote:
> I don't know enough about Pandas to explain your error, but I did
> notice that the SQL that is failing is trying to insert into a table
> called "mlstreb", but elsewhere in your code you refer to "mls_treb".
> Could that be part of the problem?
>
> To define a table from your CSV file, you could do something like this
> (untested):
>
> ###
> import csv
> import sqlalchemy as sa
>
> def get_columntype(column_definition):
> if column_definition["DataType"] == "Character":
> return sa.String(column_definition["MaximumLength"])
> elif column_definition["DataType"] == "Decimal":
> return sa.Numeric()
> # etc.
>
>
> def get_columns(csvfile):
> with open(csvfile, "r") as f:
> reader = csv.DictReader(f)
> column_definitions = list(reader)
>
> columns = []
> for cd in column_definitions:
> column = sa.Column(cd["DBName"], get_columntype(cd))
> columns.append(column)
> return columns
>
> def create_table(metadata, tablename, csvfile):
> columns = get_columns(csvfile)
> return sa.Table(tablename, metadata, *columns)
>
>
> metadata = sa.MetaData()
> res_table = create_table(metadata, "res", "res_data_dictionary.csv")
> mls_table = create_table(metadata, "mls", "mls_data_dictionary.csv")
>
> engine = sa.create_engine("...")
> metadata.create_all(engine)
>
> ###
>
> "res_table" and "mls_table" in the code above are instances of
> sqlalchemy.Table, which is part of "SQLAlchemy Core":
>
>
> https://docs.sqlalchemy.org/en/14/core/metadata.html#sqlalchemy.schema.Table
>
> If you want to use the ORM layer of SQLAlchemy, you can map classes to
> those tables something like this:
>
>
> https://docs.sqlalchemy.org/en/14/orm/mapping_styles.html#imperative-a-k-a-classical-mappings
>
> import sqlalchemy.orm as asorm
>
> mapper_registry = saorm.registry(metadata)
>
> class Res:
> pass
>
> mapper_registry.map_imperatively(Res, res_table)
>
> # Now Res will have properties for each of the columns from res_table
>
> Hope that helps,
>
> Simon
>
>
> On Thu, Feb 24, 2022 at 1:22 PM janio mendonca junior
> wrote:
> >
> > Hi Simon,
> > let me try to explain myself.
> >
> > The picture below is a sample of the 260 fields from the table res_treb
> data_dictionary.csv that I have to create.
> >
> >
> > I have started trying to create the table by defining the class table,
> and defining the fields, however I noticed that will take so long because I
> have two more tables to define with the same number of fields. Below is the
> sample code that I am manually defining (column by column) in the Res table.
> >
> >
> >
> >
> > I was thinking about something like your suggestion to read the DBName
> column from .csv, create a list and define the table columns, however I
> don't know how to do it. I know how to create the list with a DBName
> column, but how to make the attribution, for example: area_code =
> Column(String(4))?
> >
> > Second Approach
> >
> > Regarding the df.to_sql from pandas I have found two problems, how to
> define the primary key and foreign key using the command below?
> >
> > I have tried to overcome this problem by using.
> > this is the problem when I try to insert using df.to_sql: I have a row
> > and I am trying to insert on the table mls_treb if it doesn't exist
> >
> > I got this error that looks like it is related to the index but I could
> not figure it out. The field mls_number is declared as the primary key in
> my table mls_treb.
> >
> > Traceback (most recent call last):
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py",
> line 1782, in _execute_context
> > self.dialect.do_executemany(
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/sqlalchemy/engine/default.py",
> line 729, in do_executemany
> > cursor.executemany(statement, parameters)
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/cursor.py",
> line 670, in executemany
> > return self.execute(stmt)
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/cursor.py",
> line 568, in execute
> > self._handle_result(self._connection.cmd_query(stmt))
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/connection.py",
> line 854, in cmd_query
> > result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/connection.py",
> line 664, in _handle_result
> > raise
Re: [sqlalchemy] Issue "translating" raw SQL to SQLAlchemy ORM query
By default, relationship loading is deliberately not affected by your join conditions. If you want a relationship property to be restricted to the rows you've selected in your query, you need to use the "contains_eager()" query option: https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#using-contains-eager-to-load-a-custom-filtered-collection-result Hope that helps, Simon On Wed, Feb 23, 2022 at 5:19 PM shuhari2020 wrote: > > 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. -- 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/CAFHwexcQQAUt184-NvdZLv1468okgkWRnuBMaSOeMx1o_8DYmA%40mail.gmail.com.
Re: [sqlalchemy] What is the best way to declare a table with 260 columns and add rows on that table
I don't know enough about Pandas to explain your error, but I did
notice that the SQL that is failing is trying to insert into a table
called "mlstreb", but elsewhere in your code you refer to "mls_treb".
Could that be part of the problem?
To define a table from your CSV file, you could do something like this
(untested):
###
import csv
import sqlalchemy as sa
def get_columntype(column_definition):
if column_definition["DataType"] == "Character":
return sa.String(column_definition["MaximumLength"])
elif column_definition["DataType"] == "Decimal":
return sa.Numeric()
# etc.
def get_columns(csvfile):
with open(csvfile, "r") as f:
reader = csv.DictReader(f)
column_definitions = list(reader)
columns = []
for cd in column_definitions:
column = sa.Column(cd["DBName"], get_columntype(cd))
columns.append(column)
return columns
def create_table(metadata, tablename, csvfile):
columns = get_columns(csvfile)
return sa.Table(tablename, metadata, *columns)
metadata = sa.MetaData()
res_table = create_table(metadata, "res", "res_data_dictionary.csv")
mls_table = create_table(metadata, "mls", "mls_data_dictionary.csv")
engine = sa.create_engine("...")
metadata.create_all(engine)
###
"res_table" and "mls_table" in the code above are instances of
sqlalchemy.Table, which is part of "SQLAlchemy Core":
https://docs.sqlalchemy.org/en/14/core/metadata.html#sqlalchemy.schema.Table
If you want to use the ORM layer of SQLAlchemy, you can map classes to
those tables something like this:
https://docs.sqlalchemy.org/en/14/orm/mapping_styles.html#imperative-a-k-a-classical-mappings
import sqlalchemy.orm as asorm
mapper_registry = saorm.registry(metadata)
class Res:
pass
mapper_registry.map_imperatively(Res, res_table)
# Now Res will have properties for each of the columns from res_table
Hope that helps,
Simon
On Thu, Feb 24, 2022 at 1:22 PM janio mendonca junior
wrote:
>
> Hi Simon,
> let me try to explain myself.
>
> The picture below is a sample of the 260 fields from the table res_treb
> data_dictionary.csv that I have to create.
>
>
> I have started trying to create the table by defining the class table, and
> defining the fields, however I noticed that will take so long because I have
> two more tables to define with the same number of fields. Below is the sample
> code that I am manually defining (column by column) in the Res table.
>
>
>
>
> I was thinking about something like your suggestion to read the DBName column
> from .csv, create a list and define the table columns, however I don't know
> how to do it. I know how to create the list with a DBName column, but how to
> make the attribution, for example: area_code = Column(String(4))?
>
> Second Approach
>
> Regarding the df.to_sql from pandas I have found two problems, how to define
> the primary key and foreign key using the command below?
>
> I have tried to overcome this problem by using.
> this is the problem when I try to insert using df.to_sql: I have a row
> and I am trying to insert on the table mls_treb if it doesn't exist
>
> I got this error that looks like it is related to the index but I could not
> figure it out. The field mls_number is declared as the primary key in my
> table mls_treb.
>
> Traceback (most recent call last):
> File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py",
> line 1782, in _execute_context
> self.dialect.do_executemany(
> File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/sqlalchemy/engine/default.py",
> line 729, in do_executemany
> cursor.executemany(statement, parameters)
> File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/cursor.py",
> line 670, in executemany
> return self.execute(stmt)
> File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/cursor.py",
> line 568, in execute
> self._handle_result(self._connection.cmd_query(stmt))
> File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/connection.py",
> line 854, in cmd_query
> result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
> File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/mysql/connector/connection.py",
> line 664, in _handle_result
> raise errors.get_exception(packet)
> mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column
> 'mls_number' in 'field list'
> The above exception was the direct cause of the following exception:
> Traceback (most recent call last):
> File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/sql.py",
> line 1419, in to_sql
> raise err
> File
>
