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
<[email protected]> 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 <[email protected]> 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
>> <[email protected]> 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 <[email protected]> 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
>> >> <[email protected]> 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 [email protected].
>> >> > 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 [email protected].
>> >> 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 [email protected].
>> > 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 [email protected].
>> 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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexcHUtTyxH60UESJsAqU9izMXnBp9ghRTLfAST%3Ddb7sB2w%40mail.gmail.com.
