how to load data into existing database tables, using sqlalchemy?

One possible solution using SQLAlchemy metadata would go like this:

In[7]:

   from sqlalchemy.schema
import MetaData
meta = MetaData()
meta.reflect(bind = engine)

In[20]:

   for t in meta.tables:
   for x in engine.execute(meta.tables[t].select()):
   print x(1, u 'one')
   (2, u 'two')
   (1, 1, 1)
   (2, 1, 2)
   (3, 2, 0)
   (4, 2, 4)

In MetaData(), tables is a dictionary of the tables in the schema. The dictionary goes by table name, and it is actually very similar to the dictionary in your code. You could iterate the metadata like this,

for table_name, table in meta.tables.items():
   for x in engine.execute(table.select()):
   print x

or, trying to adapt it into your code, you could just do something like,

for table_name in ordered_tables:
   conn.execute(meta.tables[table_name].insert_many(system_data[table_name]))

This is the solution I used:

from sqlalchemy
import create_engine, MetaData
# from myplace
import load_current_system_data() and other relevant functions

def alchemy_current():
   system_data = load_current_system_data()
engine = create_engine('mysql+pymysql://user:password@localhost/your_db_name')
meta = MetaData()
meta.reflect(bind = engine)
conn = engine.connect()
for table_name in ordered_tables:
   conn.execute(meta.tables[table_name].insert().values(system_data[table_name]))
conn.close()
# engine.dispose()

print("System's Data successfully loaded into Database!")

you could import text:

from sqlalchemy.sql
import text

and then execute the following

    conn.execute(text("mysql commands goes in here"))

example for insert:

    conn.execute(text("INSERT INTO `table_name`(column_1,column_2,...) VALUES (value_1,value_2,...);"))

Suggestion : 2

Python Python Home date Samples String List Math Functions Built in Functions File Handling Error Handling Class Object Tkinter Numpy Pandas Python & MySQL SQLite , Post your comments , suggestion , error , requirements etc here

database_name

from sqlalchemy
import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")

from sqlalchemy
import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")

id = my_conn.execute("INSERT INTO  `database_name`.`student` (`name` ,`class` ,`mark` ,`sex`) \
                  VALUES ('King1',  'Five',  '45',  'male')")
print("Row Added  = ", id.rowcount)

Row Added = 1

Using parameterized query to add record

from sqlalchemy
import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")

query = "INSERT INTO  `database_name`.`student` (`name` ,`class` ,`mark` ,`sex`) VALUES(%s,%s,%s,%s)"
my_data = ('King', 'Five', 45, 'male')

id = my_conn.execute(query, my_data)
print("ID of Row Added  = ", id.lastrowid)

Using multiple records to add

from sqlalchemy
import create_engine
my_conn = create_engine("mysql+mysqldb://userid:password@localhost/database_name")
try:
query = "INSERT INTO  `database_name`.`student` (`name` ,`class` ,`mark` ,`sex`)  VALUES(%s,%s,%s,%s)"
my_data = [('King', 'Five', 45, 'male'),
   ('Queen', 'Four', 44, 'Female'),
   ('Jack', 'Three', 42, 'male')
]

id = my_conn.execute(query, my_data)

print("Rows Added  = ", id.rowcount)

except:
   print("Database error ")

Suggestion : 3

The SQL Expression Language constructs its expressions against table columns. SQLAlchemy Column object represents a column in a database table which is in turn represented by a Tableobject. Metadata contains definitions of tables and associated objects such as index, view, triggers, etc.,Column object represents a column in a database table. Constructor takes name, type and other parameters such as primary_key, autoincrement and other constraints.,An object of Table class represents corresponding table in a database. The constructor takes the following parameters −,The text() construct allows us to link its textual SQL to Core or ORM-mapped column expressions positionally. We can achieve this by passing column expressions as positional arguments to the TextClause.columns() method.

Any version of Python higher than 2.7 is necessary to install SQLAlchemy. The easiest way to install is by using Python Package Manager, pip. This utility is bundled with standard distribution of Python.

pip install sqlalchemy

In case of anaconda distribution of Python, SQLAlchemy can be installed from conda terminal using the below command −

conda install - c anaconda sqlalchemy

It is also possible to install SQLAlchemy from below source code −

python setup.py install

The create_engine() function takes the database as one argument. The database is not needed to be defined anywhere. The standard calling form has to send the URL as the first positional argument, usually a string that indicates database dialect and connection arguments. Using the code given below, we can create a database.

>>> from sqlalchemy
import create_engine
   >>>
   engine = create_engine('sqlite:///college.db', echo = True)

For a MySQL database, use the below command −

engine = create_engine("mysql://user:pwd@localhost/college", echo = True)

Suggestion : 4

Metadata - Generating Database Schema,Close database connection,Inspect - Get Database Information,Get Table from MetaData

from sqlalchemy.engine.url
import URL

postgres_db = {
   'drivername': 'postgres',
   'username': 'postgres',
   'password': 'postgres',
   'host': '192.168.99.100',
   'port': 5432
}
print(URL( ** postgres_db))

sqlite_db = {
   'drivername': 'sqlite',
   'database': 'db.sqlite'
}
print(URL( ** sqlite_db))

$ python sqlalchemy_url.py
postgres: //postgres:[email protected]:5432
   sqlite: ///db.sqlite

from sqlalchemy
import create_engine

db_uri = "sqlite:///db.sqlite"
engine = create_engine(db_uri)

# DBAPI - PEP249
# create table
engine.execute('CREATE TABLE "EX1" ('
   'id INTEGER NOT NULL,'
   'name VARCHAR, '
   'PRIMARY KEY (id));')
# insert a raw
engine.execute('INSERT INTO "EX1" '
   '(id, name) '
   'VALUES (1,"raw1")')

# select *
   result = engine.execute('SELECT * FROM '
      '"EX1"')
for _r in result:
   print(_r)

# delete *
   engine.execute('DELETE from "EX1" where id=1;')
result = engine.execute('SELECT * FROM "EX1"')
print(result.fetchall())

from sqlalchemy
import create_engine

db_uri = 'sqlite:///db.sqlite'
engine = create_engine(db_uri)

# Create connection
conn = engine.connect()
# Begin transaction
trans = conn.begin()
conn.execute('INSERT INTO "EX1" (name) '
   'VALUES ("Hello")')
trans.commit()
# Close connection
conn.close()

from sqlalchemy
import create_engine
from sqlalchemy
import MetaData
from sqlalchemy
import Table
from sqlalchemy
import Column
from sqlalchemy
import Integer, String

db_uri = 'sqlite:///db.sqlite'
engine = create_engine(db_uri)

# Create a metadata instance
metadata = MetaData(engine)
# Declare a table
table = Table('Example', metadata,
   Column('id', Integer, primary_key = True),
   Column('name', String))
# Create all tables
metadata.create_all()
for _t in metadata.tables:
   print("Table: ", _t)

from sqlalchemy
import create_engine
from sqlalchemy
import inspect

db_uri = 'sqlite:///db.sqlite'
engine = create_engine(db_uri)

inspector = inspect(engine)

# Get table information
print(inspector.get_table_names())

# Get column information
print(inspector.get_columns('EX1'))