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 <si...@simonking.org.uk> 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
> <janio...@gmail.com> 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
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/sql.py",
> line 1411, in to_sql
> > table.insert(chunksize, method=method)
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/sql.py",
> line 845, in insert
> > exec_insert(conn, keys, chunk_iter)
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/pandas/io/sql.py",
> line 762, in _execute_insert
> > conn.execute(self.table.insert(), data)
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py",
> line 1289, in execute
> > return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/sqlalchemy/sql/elements.py",
> line 325, in _execute_on_connection
> > return connection._execute_clauseelement(
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py",
> line 1481, in _execute_clauseelement
> > ret = self._execute_context(
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py",
> line 1845, in _execute_context
> > self._handle_dbapi_exception(
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/sqlalchemy/engine/base.py",
> line 2026, in _handle_dbapi_exception
> > util.raise_(
> > File
> "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/sqlalchemy/util/compat.py",
> line 207, in raise_
> > raise exception
> > 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)
> > sqlalchemy.exc.ProgrammingError:
> (mysql.connector.errors.ProgrammingError) 1054 (42S22): Unknown column
> 'mls_number' in 'field list'
> > [SQL: INSERT INTO mlstreb (mls_number, `0`) VALUES (%(mls_number)s,
> %(0)s)]
> > [parameters: ({'mls_number': 'mls_number', '0': 'C5405199'},
> {'mls_number': 'active', '0': True}, {'mls_number': 'class_name', '0':
> 'RES'}, {'mls_number': 'active_date', '0': datetime.date(2022, 1, 3)})]
> > (Background on this error at: https://sqlalche.me/e/14/f405)
> >
> > On Thu, Feb 24, 2022 at 4:01 AM Simon King <si...@simonking.org.uk>
> wrote:
> >>
> >> Before we do that, you said that you tried pandas dataframe.to_sql but
> >> it didn't work - can you explain what you mean? Did it raise an error,
> >> or produce the wrong result, or something else?
> >>
> >> Simon
> >>
> >> On Wed, Feb 23, 2022 at 9:13 PM janio mendonca junior
> >> <janio...@gmail.com> wrote:
> >> >
> >> > 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 <si...@simonking.org.uk>
> 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
> >> >> <janio...@gmail.com> 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
> .
> >>
> >> --
> >> 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/CAFHwexfbN8Wt6MPp1PY6-gptjL5zdMmjy5z5YAFsczdgES%2B8Uw%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/CADF7wwbR5mSkPHJ-sRQJGKzp9i2Xk_thj4UhbRVhwSsZfYshkw%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/CAFHwexcHUtTyxH60UESJsAqU9izMXnBp9ghRTLfAST%3Ddb7sB2w%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/CADF7wwaR8%2B7Dn_Fyop5x2RNES-Qf3t_wRnYymOO4w0b_N8VNOg%40mail.gmail.com.
